 # Formulas: More than 7 IF statements - what should I do?

• I am tring to write a formual to calucate commission for sales of an item. I started with an IF statement and found that i have more than 7 statements which makes the IF statement unusable.

Here is the deal.
1) for up to 250K the commission is 10%
2) on the next portion up to 500K is 7%
3) on the next part upto 1000K is 5%
4) on the next part upto 2000K is 3%
5) on the rest over 2000K is 2%

Example:

If the sales price = 1200K
1) = 25K (250K*10%)
2)= 17.5K (500-250K = 250K * 7%)
3) = 25 K (1000K -500K = 500K * 5%)
4) = 6K (1200k - 1000K = 200 K *3%)
= 73.5K

Just in case K=1,000

• In your post there are not more than 7 levels. Given the information in the post, this formula should give you what you need:

=IF(A1<250,A1*0.1,IF(AND(A1&gt;250,A1<=500),(25+(A1-250)*0.07),IF(AND(A1&gt;500,A1<=1000),(25+17.5+(A1-500)*0.05),IF(AND(A1&gt;1000,A1<=2000),(25+17.5+25+((A1-1000)*0.03)),(25+17.5+25+30+((A1-2000)*0.02))))))

Adjust the A1 reference to whatever cell has your sales price in it. Also, multiply by 1000 if you need the actual number, not the abbreviated "K" version.

HTH
:usaflag:

• You can use the CONCATENATE function in front of IF, this will allow you to nest up to 39 IF functions.

• A similar problem was posed earlier. See my example workbook in the next to last entry that uses the vlookup solution and can handle as many rates as needed. I think it is easier than Pearson's progressive pricing example referred to above. Warning, the referenced thread is very wide so be prepared to scroll.

• Thank you for all of your responses.

I tried the first one. I copied the formula into excel and the result was not 73.5K it was 116 or something so something is missing there. I feel that the sample work book link that was provided by Derk may work for me I will try it our and let everyone know

• I fixed Brantock's formula:

=IF(A1<250,A1*0.1,IF(AND(A1&gt;250,A1<=500),(25+(A1-250)*0.07),IF(AND(A1&gt;500,A1<=1000),(25+17.5+(A1-500)*0.05),IF(AND(A1&gt;1000,A1<=2000 ),(25+17.5+25+((A1-1000)*0.03)),(25+17.5+25+30+((A1-2000)*0.02))))))*1000

Thanks for the help

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!