 • 461K Members
• 6,085 Online
• 559K Conversations

# 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 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

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

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)

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

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?

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

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})

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

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.

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

If only \$10000 is not the text...

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies