Nested IF formula for sliding commission scale

[tmva]

I am trying to create a sliding commission scale where the following occurs:

The formula will be in Cell E45 and need to return a value of "0", "6%", "8%", "10%" based on Gross profit Percentage.
Gross profit is cell I50

Example if Gross Profit is less than 50%, then 0 commission.
If Gross Profit is 50-60%, then 6% commission
If Gross Profit is 60-70% then 8% commission
If Gross Profit is 70% and greater then 10% commission.

I have come up with the below formula but unable to get that last statement to work once a gross profit of 70% or more is reached.

=IF(I50<50%,"0",IF(I50<=60%,"6%",IF(I50>=60%-69%,"8%",IF(I50>=70%,"10%"))))

Does anyone have an suggestions on the correct formula?

[gmr4evr1]

Maybe this....

Please Login or Register  to view this content.

[Sam Capricci]

maybe a little simpler... =IF(I50<=50%,0,IF(I50<=60%,6%,IF(I50<=70%,8%,10%)))

[tmva]

A quick try here and it worked! Thanks to both of you! You don't know how long I racked my brain to try to figure it out! Thanks again!

[gmr4evr1]

@ Sambo kid - Wouldn't there be an issue with doing it that way? If the number in I50 is 59%, then it would meet 3 of the 4 criteria, as it is less than 50, 60 and 70.

[gmr4evr1]

No, problem, glad to help.

[Sam Capricci]

gmr4evr1

IF formulas stop working once it finds the first criteria that it meets so at 59.99% it returns 6% only.

[gmr4evr1]

Ahhh, ok, that makes sense.

[Sam Capricci]

gmr4evr1
No problem.
after I thought about it I should've wrote that most formulas stop working once the criteria is met like a vlookup, index/match etc will not go past the first instance it finds that meets the criteria unless it is designed to look for a second or third instance, sumif/s countif/s as obvious exceptions as are others.

[gmr4evr1]

I guess doing it like that one would have to be sure they put the criteria in the correct order within the formula.

[Sam Capricci]

I guess doing it like that one would have to be sure they put the criteria in the correct order within the formula.

yes, you would have to have it in ascending order, if backward then the first criteria it would meet would be the less than or equal to 70% and return 8%.

[FlameRetired]

Perhaps another way.

Please Login or Register  to view this content.