Feb 11 2019 09:32 AM
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?
Feb 11 2019 10:54 AM
Feb 11 2019 11:22 AM
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?
Feb 11 2019 12:14 PM
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})
Feb 11 2019 05:50 PM
@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.
Feb 12 2019 01:15 AM
If only $10000 is not the text...