IF calculation not working...what am I missing?

[mbrown3039]

Hello all, I was recently offered a sales job where the commission rate is based on the profit margin of the sale (specifically, no commission on sales with less than 20% margin, 20-29% margin = 10% commission, 30-32% = 12.5% comm., 33-39% = 15% and 40%+ margin = 18% commission); in trying to see what that equates to based on my previous year's sales, i used this formula (where j2 = margin % and i2 = margin dollars):

=IF(J2>19%,I2*10%,IF(J2>29,I2*12.5%,IF(J2>32%,I2*15%,IF(J2>39%,I2*18%))))

However, the formula stops at the first commission rate (10%) for all sales regardless of the actual margin shown in j2. I am not an Excel expert by any means (I think this is only the 3rd time I've ever used the IF function) and am stumped as to why it stops at the 1st commission rate. Any thoughts? Thanks in advance, M

 

[Tetra201]

Try this:

=I2*LOOKUP(J2,{0,0.2,0.3,0.33,0.4},{0,0.1,0.125,0.15,0.18})

 

[FastFixFox]

Change the order you are searching in. You need to see if it is higher than the highest margin first, then the second highest, and so on. That formula will return 10 anytime margin is over 19%, it will not reach the next IF statement.

Sorry, no formula.. away from PC.

 

[mbrown3039]

Try this:

=I2*LOOKUP(J2,{0,0.2,0.3,0.33,0.4},{0,0.1,0.125,0.15,0.18})

Thanks, Tetra -- that seems to work.

FastFixFox - for the sake of my own learning, I'm adding another column to try your suggestion -- thanks for the tip! M