Multiple Conditions Formula Help Needed

[chiwavdg]

I currently have a formula that I have used to calculate commisio
rates. We had a 2 rate plan. If sales were below a certail threshol
then apply this rate and if above apply this rate. I used an IF/THE
formula. We have recently switched to a 4 rate plan, and I am unsur
how to create a formula to reflect this.

Please advise..

thanks

dav

 

[Myrna Larson]

One approach would be a table to use with VLOOKUP. In the formula, where you
now have a rate, use VLOOKUP.

 

[filky]

I don't know the details of your plan but try something like this.

Cell A1 - variable earnings typed in
Cell A2 - =OFFSET($C$2,MATCH($A1,{100,150,200,250}),0)
Cell A3 - =SUM(A1:A2)
Cell C3 - =+$A$1*10%
Cell C4 - =+$A$1*20%
Cell C5 - =+$A$1*30%
Cell C6 - =+$A$1*40%

Idea is that you enter your earnings into A1. If you earn over 99 an
up to 149 then add 10% to your earnings; up to 199 add 20% etc.

You should be able to adapt the principle to suit your needs

 

[Guest]

=IF(Sales>Threshold1,IF(Sales>Threshold2,IF(Sales>Threshold3,CommissionRate1,CommissionRate2),CommissionRate3),CommissionRate4) Should work.

unlikeKansas