Nested IF functions and 3 conditions

[Guest]

I am trying to nest 3 conditions for a commission pay structure: $0-$15,000
= 25%; $15,000-$30,000 = 30%; $30,000-$50,000 = 35%. D6 is the total
earnings upon which the commission is based. So, for the $15k-$30K
possibility I have:
=if(D6<15000,"0",if(D6>30000,(15000*.30)),(d6-15000)*.30)
Excel's error says there are too many arguments. If an employee earns $50K,
they would receive (15,000 * 25%) + (15,000 * 30%) + (20,000 * 35%). But if
they earn $10,000, the commission is ($10K * 25%) and all the other levels
need to have a zero (instead of FALSE as is currently occurring). Any help
on nesting IF functions would be greatly appreciated.
thanks, Dixie

 

[Peo Sjoblom]

Take a look here


http://www.mcgimpsey.com/excel/variablerate.html

 

[Guest]

Excellent solution to find the commission with the various levels grouped
together. However, I need to see each commission broken out at each level.
For example, if the sales is $60K, I need the breakdown for commission for
the first $15K at 25%, then commission for $15k-$30K at 30%, commission for
$30K-$50K at 35%, commission for $50K-$80K at 40%, then portion over $80K at
45%. Are you able to "ungroup or un-nest" the solution so that they apply
only per commission band?

 

[Myrna Larson]

Do you mean that you want 5 columns with the commission for each of the 5
brackets?

If so, you could do something like this. With the amount in column A, put the
numbers 0, 15000, 30000, 50000, and 80000 in B1:F1. In B2:F2 put the
corresponding percentages, 25%, 30%, 35%, 40%, and 45%

Then put the sales in A3. In B3 put this formula:

=MAX(($A3-B$1)*B$2,0)

and copy it to the right through F3. Then B3:F3 down as far as you need.
Adjust the formulas to suit your layout.

 

[Myrna Larson]

Disregard the formula below. It isn't correct.

Do you mean that you want 5 columns with the commission for each of the 5
brackets?

If so, you could do something like this. With the amount in column A, put the
numbers 0, 15000, 30000, 50000, and 80000 in B1:F1. In B2:F2 put the
corresponding percentages, 25%, 30%, 35%, 40%, and 45%

Then put the sales in A3. In B3 put this formula:

=MAX(($A3-B$1)*B$2,0)

and copy it to the right through F3. Then B3:F3 down as far as you need.
Adjust the formulas to suit your layout.

 

[Myrna Larson]

Try this formula instead:

=IF($A3<B$1,0,MIN(C$1-B$1,$A3-B$1)*B$2)

You need to modify the layout I described earlier: in $G1 put some impossibly
high number, say $100,000,000 or greater.

 

[Guest]

EXCELLENT solution, Myrna. It worked great. Not sure why $G1 needs such a
high number, but the formula works so I am VERY happy. Thanks for the help.

 

[Myrna Larson]

G$1 just has to be higher than any possible value in column A. You could in
fact put a formula in G$1: =MAX(A:A)

The last formula, in F3, is

=IF($A3<F$1,0,MIN(G$1-F$1,$A3-F$1)*F$2)

You want to ensure that the number in G1 high enough that $A3 will always be
smaller than G$1 so the commission for the last bracket is calculated on the
difference between the actual sales (in $A3) and $80,000.

If you were to put, say, $100,000 in G$1, and the sales were $125,000, you
would calculate 45% of G$1-F$1 ($20,000), instead 45% of of $A3-F$1 ($45,000).