## If function

504 Views 8 Replies Latest reply: May 16, 2012 10:39 AM by Barry
Level 1 (0 points)
Currently Being Moderated
May 13, 2012 10:20 AM

What formula would I use for \$25 per thousand for \$0 to \$100,000 and \$15 per thousand for \$100,000 to \$500,000, etc.  I am new to the If function

MacBook Pro, Mac OS X (10.7.3)
• Level 7 (29,095 points)
Currently Being Moderated
May 13, 2012 4:41 PM (in response to Haddy234)

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.

C3: =B2+INT(A3/1000)*C2

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.

=E*INT((B-C)/1000)

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:

=D+F

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.

Regards,

Barry

• Level 7 (29,095 points)
Currently Being Moderated
May 14, 2012 1:57 AM (in response to Haddy234)

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.

Lookup table

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.

B3: =B2+(A3-A2)*C2

B4: =B3+(A4-A3)*C3

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.

Examples table:

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:

=E*(B-C)

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.

Regards,

Barry

• Level 6 (10,765 points)
Currently Being Moderated
May 14, 2012 6:08 AM (in response to Haddy234)

If you want a single formula, I think this is one:

=SUM(0.75%*B2, 0.25%*MIN(B2,2000000),0.5%*MIN(B2,500000),1%*MIN(B2,100000))

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.

• Level 7 (29,095 points)
Currently Being Moderated
May 14, 2012 12:17 PM (in response to Badunit)

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))

Regards,

Barry

• Level 7 (29,095 points)
Currently Being Moderated
May 16, 2012 10:39 AM (in response to Haddy234)

oops

Thought there might be an 'Oops' involved there.

Chalk it up to experience, and don't fret it overly much. Oopses tend to average out in the long term.

Regards,

Barry

#### More Like This

• Retrieving data ...

#### Legend

• This solved my question - 10 points
• This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.