Forum Discussion

Madeline Clancy

Copper Contributor

Feb 11, 2019

Error with IF formula (testing 3 variables and entering the specified value. if one is true.)

I have an IF formula in excel to asses bonus structure based on sales. The 3 "tests" are $7500= $50 bonus $10,000= $75 bonus $12500= $100 bonus For example: Sales= $9,100 which should test a...

Iron Contributor

Feb 11, 2019

Try using the IFS() function. F3 is the cell where the value for Sales is in my example.

=IFS(F3>=12500,100,F3>=10000,75,F3>=7500,50,F3<7500,0)

Madeline Clancy

Copper Contributor

Feb 11, 2019

Hi Ed, thanks for your help! I tried what you suggested, however the conversion for $10000 sales is not showing 75. Here is the formula I have entered: =IFS(F3>=12500,100,F3>=10000,75,F3>=7500,50,F3<7500,0)

Do you happen to see why that would not be correctly evaluated?

Ed Hansberry
Iron Contributor
Feb 12, 2019
Madeline Clancy wrote:
Hi Ed, thanks for your help! I tried what you suggested, however the conversion for $10000 sales is not showing 75. Here is the formula I have entered: =IFS(F3>=12500,100,F3>=10000,75,F3>=7500,50,F3<7500,0)
Do you happen to see why that would not be correctly evaluated?

No. It should work. When I type in 10,000 it works fine, correctly returning $75. Are you typing in 10,000 or is it a formula? It could be one of those things where the formula is returning 9,999.9999999997 or something due to how excel calculates. You may need to wrap your sales formula in a ROUND(n,2) function, or wrap F3 above in round(F3,2) in each instance.
- SergeiBaklan
  Diamond Contributor
  Feb 12, 2019
  If only $10000 is not the text...
IngeborgHawighorst
MVP
Feb 11, 2019
That formula works fine. Check that what you enter in F3 is a number that is greater than ten thousand.

You could also use a lookup function instead of nested IF or IFS.

=LOOKUP(F3,{0,7500,10000,12500},{0,50,75,100})

Resources