Need help with some rather complex formulas...

[Dan B]

Complex to me anyway...

I'm trying to create a commission schedule that will calculate at multiple
levels. The first level is $7500, the second level is $17,500 more then the
first, the third level is $25,000 more then the second, then anything over
that. Each level is a separate formula. For example, if there is a sale
for $100,000 I need to calculate 8% of the first $7500, then 7% on the next
$17,500, then 6.5% on the next $25,000, then 6% on anything thereafter. It
also needs to check another cell to see if its value is 50 or under.

Here is my initial formula for the first level, which obviously doesn't give
the desired result:

=IF (c14<51,IF(AND(R14<7500.01),R14*$BI$4,""))

In my data, C14=7 and R14=$64,832, so it returned 5186.55, because both
conditions were true. But I needed to just give me 8% of the first $7500
out of the $64,832, which is $600

My second level formula would need to calculate 7% of the next $17500 after
the first $7500, which is $1225

The third level formula would calculate 6.5% of the next $25000 after the
initial $7500 and next $17500

The next formula would calculate 6% on the balance in this case of $14,832,
which is $964.08

BI4 is where the 8% comes from, BI5 is the 7% and so on.

As a side note, there is another section in here that is for when C14 is
over 50 that calculates higher sale levels at different percentages. Same
idea, just higher numbers.

I hope that makes at least some sense.

Thanks!!!

Dan

 

[Guest]

You can calculate the total commission with one formula....see here

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

 

[Guest]

....or another way to calculate the same thing, leaving out for a minute your
check of C14.....

=R14*6%+MIN(50000,R14)*0.5%+MIN(25000,R14)*0.5%+MIN(7500,R14)*1%

 

[Dan B]

Thanks for this formula, but I need to have each level broken out
separately. I tried to take your formula and split it up like so:

=IF(C14<51,MIN(7500,R14)*8%)
=IF(C14<51,MIN(25000,R14)*7%)
=IF(C14<51,MIN(50000,R14)*6.5%)

But this will not get what I need and it does not allow for the 4th level
calculation which is what ever the remaining balance is, if any. Based on
the amount of $64832, your formula showed a commission on 4339.92, which is
correct, but I do need it in 4 different formulas. Can that be done?

Thanks a ton for your help this far.