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:

  • 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!