Home

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

Madeline Clancy
New Contributor

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 and equate to "$50" with the following formula: 

=IF(B30>=12500,"$100",IF(B30>=10000,"$75",IF(B30>=7500,"$50","FALSE")))

I am finding that weather I order the formula least t greatest or greatest to least it is only populating the cell with the specified answer from the 1st test in the formula. In this case, it's entering $50- or if I put the test for $12500 first, it populates with "$100" even though the value does not meet that rule. How can I fix this to test for all 3 and populate correctly based on the value?

 

5 Replies
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)

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? 

 

That formula works fine. Check that what you enter in F3 is a number that is greater than ten thousand.

 

2019-02-12_09-10-47.png

 

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

 

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


@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.

 

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies