I would use MATCH and OFFSET rather than a set of nested IFs, especially if there are more than three steps. Placint the thresholds, fees at thresholds and rates for the step below that threshold onto a separate table makes for editing that is far easier than entering that information dirctly intro a set of nested IFs.
Here's an example. Formulas below.
The smaller table is the Lookup table. Columns A and C contain directly entered data. Column B contains entered data in B2 ( zero ), and a formula in the rest of the cells showing values.
Fill this formula down to the end of the data in column A.
The larger table is the Data table. It contains several rows in this demonstration example to allow showing the calculations of a range of fees. The and data below are all in row 2.
B2 contains the amount on which the fees are to be calculated.
C2 separates out the base amount (ie the amount up to the largest threshold amount less than the amount in B2, using Match to find the threshold, then OFFSET to return that threshold amount.
=OFFSET(Lookup :: $A$1,MATCH($B,Lookup :: $A)-1,COLUMN()-3)
D2 and E2 contain the same formula as B2, making the same MATCH as the earlier formula, but returning the values from the next two columns.
F2 contains a formula that calculates the Fee on the amount that exceeds the threshold amount in C2, and the amount per thousand in E2.
Note that this counts only full thousands—the 6999 in B2 produces the same calculated marginal fee as would an amount of 6000.
G2 adds the Base fee in column D and the Fee on Marginal amount in column F to calculate the full fee:
Discussion of the functions used may be found in the iWork formulas and Functions User Guide, which may be downloaded via the link in the Hlp menu in Numbers '09.
That doesn't sound like it will do exactly what I am needing, especially since it only counts in full thousands. I am an estimator for a construction company and at the end of my bids I need to add bid, performance and payment bonds to my estimates. My bonding company charges me 2.5% for everything under $100k, then 1.5% for all money $100k - $500k, then 1% for $500k - $2 mil and then .75% for everything over $2 mil.
So for a $750,000 job I pay $2,500 (2.5%) for the first $100k portion, $6,000 (1.5%) for the next $400,000 portion, and then the remaining $250k I would pay $2,500 (1%). This is a simple example, but what if my bid is $342, 954? then I would be $2,500 for the first $100k and then $3,644.31 for the reminder (1.5% of the $242,954) for a total of $6,144.31. Plus I don't want to do this manually every time I place a bid.
It 'doesn't do exactly what you are needing' because you did not specify 'exactly what you are needing' in your initial problem statement.
You specified the rates as "per thousand", gave the first two rates, and added "etc.", implying that there was more than one rate change beyond that.
If your current specification is correct and complete, then the revisions below should provide correct results.
Your two provided cases are shown in the green-filled rows:
Revisions: Rates are now specified as percentages, and applied to the actual amounts in the amount column rather than blocks of 1000.
Column A: Break points at which the fee rate changes.
Column B: B2 contains the minimum fee charged (zero).
B3 contains a formula that calculates the (total) fee at the break point listed in A3.
B4 contains the same formula, filled down to the next row.
Column C contains a list of the rates charged, expressed as a percent, on amounts between the breakpoint in the same row and the one in the next row.
Nothing in this table changes unless the bonding company changes its rate schedule.
Column A is a Header column, usually used for labels.
Column B contains the amount of the bid. The Examples table contains several rows so that a range of examples may be presented and examined. For purposes of calculating the fees on a single bid, only a single row would be required. The bid would be entered in column B.
Column C checks the Lookup table to find the largest Base amount equal to or less than the bid amount. The formula for this column is unchanged from that shown in my initial post.
Column D uses the same formula to check the Lookup table to find the fee charged on the Base amount identified in column C. Note that the fee listed for the 500,000 Base amount/Breakpoint includes the 2500 charged on the first 100,000 plus the 6000 charged on the next 400,000. The formula for this column is unchanged from that shown in my initial post.
Column E uses the same formula to check the lookup table for the fee rate to be charged on the marginal amount (the portion of the bid that exceeds to Base amount determined in Column C). The formula for this column is unchanged from that shown in my initial post.
Column F calculates the fee to be charged on the marginal amount. The formula for this column is simpler due to the rate being expressed as a percentage, and being applied on the actual amount:
Column G calculates the total fee by adding the fee on the base amount (in column D) and the fee on the Marginal amount (in column F). The formula for this column is unchanged from that shown in my initial post.
If you want a single formula, I think this is one:
It builds the cost from the other direction than how you might normally calculate it. The entire bid has 0.75% applied to it, anything under $2M has an additional .25% applied to it, anything under $500K has an additional 0.5% applied, and anything under $100K has an additional 1% applied.
Ingenious! It certainly makes for a more compact formula than my approach, even when the process above is placed into a single formula in a single cell (see below), but it does add some complexities to editing, should the rates change.
One consideration I thought important was to keep the data specifying the fee shedule open and editable by placing it into a lookup table.
Using several columns in my Examples table allowed ''seeing" the elements making up the total fee. In practice, the calculations could be combined in a single formula to calculate the Total fee directly, as below. Bid amount is in B2.
=OFFSET(Lookup :: $A$1,MATCH(B2,Lookup :: $A)-1,1)+OFFSET(Lookup :: $A$1,MATCH(B2,Lookup :: $A)-1,2)*(B2-OFFSET(Lookup :: $A$1,MATCH(B2,Lookup :: $A)-1,0))
This does exactly what I need! Thank you. I cant read it or understand it, but it gives me the right answer so I am happy! I do see the challenge if the rates change, but I can always go into the actual cell and change the formula easy enough. Thanks for the advice to both of you