Hello there,
I can problems to figure out how to write nested if function correctly.
Tried many variations and still no result.
I need to create a formula like this:
If sales are more then 12000 EUR, write 900
If sales are more then 16000 EUR, write 1100
If sales are more then 20000 EUR, write 1300
If sales are more then 24000 EUR, write 1400
Tried all variations and cannot work it out.
Please help
Hi , Assuming your value is in A6,
Try =IF(A6>24000,1400,IF(A6>20000,1300,IF(A6>16000,1100,IF(A6>12000,900,0))))
Let us know if it helps.
Thank you very much.
It works!
Hello
to avoid nesting you may use (assuming value in cell A1)
=IFS(A1>24000,1400,A1>20000,1300,A1>12000,900,A1<1200,0)
Note: The first condition met will evaluate, thus the order of the conditions and their respective values is important.
Update - thanks to @mikekaganski, noticed that I’ve left a “0” and it should read:
=IFS(A1>24000,1400,A1>20000,1300,A1>12000,900,A1<=12000,0)
which leaves values from 1200 to 12000 inclusive undecided ;-D
For “default” case (like “what to do in all other cases”), just use 1 or TRUE() as the condition for the last value.
Thank you so much for your help!
Got a solution