If Statements - Commission Plan

[Guest]

=IF(D14=0,"",IF(D14>2500000,">1!!",D14*$A$7))
This is a statement on a commission plan. I have 5 levels in the plan. (see
below)
6% of gross sales up to $1,200,000
7% of gross sales between $1,200,0001-$1,500,000
8% of gross sales between $1,500,0001-$2,000,000
9% of gross sales between $2,000,0001-$2,500,000
10% of gross sales over $2,500,0001
In my statement above it works but only for one level, I need to know how to
add the different levels

 

[Gary Brown]

The formula would be...

=IF(D14=0,0,(0.06*MIN(D14,1200000)))+IF(D14>1200000,0.07*
(MIN(D14,1500000)-1200000),0)+IF(D14>1500000,0.08*(MIN
(D14,2000000)-1500000),0)+IF(D14>2000000,0.09*(MIN
(D14,2500000)-2000000),0)+IF(D14>2500000,0.1*(D14-
2500000),0)

You can, of course, change the percentages such as .06
and .07 for cell references such as $A$7. For that
matter, you can also change the levels for cell references
so the formula looks something like...

=IF(D14=0,0,($B$14*MIN(D14,$C$14)))+IF(D14>$C$14,$B$15*(MIN
(D14,$C$15)-$C$14),0)+IF(D14>$C$15,$B$16*(MIN(D14,$C$16)-
$C$15),0)+IF(D14>$C$16,$B$17*(MIN(D14,$C$17)-$C$16),0)+IF
(D14>$C$17,$B$18*(D14-$C$17),0)
where column B contains the percentages, column C
contains the levels and column D contains the actual sales
used for calculating the commissions.


HTH,
Gary Brown