Multiple IF formula is throwing an error

Question

I'm trying to calculate a number of potential outputs in Excel, which I had working - but then I wanted to add a NULL output if the cell contained 0.

Currently, I'm trying to provide the following output for one cell, based on data in a cell to the left:

If the figure is lower than 3% it'll spit out "Low"
If it's between 3 & 5%, it'll say "Medium"
And if it's more than 5%, it'll say "High"

I've managed to get the following to work:

=IF(C18>5%,"High",IF(C18<3%,"Low",IF(AND(C18>=3%,C18<=5%),"Medium")))

But now I want to add in a null volume for if the box doesn't contain a value. I tried:

=IF(C18>5%,"High",IF(C18<3%,"Low",IF(AND(C18>=3%,C18<=5%),"Medium",IF(C14=0,"Null"))))

but it won't work, and I'm stumped to what I can do to fix it. I had added in another "IF(AND" for "Low", but that threw things off completely.

I think you need to reorder the IFs - check for NULL first, then check for high, then check for low, and if all those fail then it's medium. — Tim, Nov 27 '15 at 17:28
Don't use "Null". Doing so will result in looking for a string value — Ian, Nov 27 '15 at 17:39

score 1 · Answer 1 · answered Nov 27 '15 at 17:37

1

Please try:

=IF(C18=0,"",IF(C18<0.03,"Low",IF(C18<0.05,"Medium","High")))

but adjust the break points to suit.

answered Nov 27 '15 at 17:37

pnuts

58,317
11
87
139

score 0 · Accepted Answer · answered Nov 27 '15 at 17:37

Try rearranging the order of your tests. This will give you the results you're looking for:

=IF(C14=0,"Null",IF(C18>5%,"High",IF(C18<3%,"Low","Mediunm")))

First check to see if C14 is 0, as that will evaluate to NULL. If it isn't 0, then check to see if C18 is greater than 5%, which results in High. If it's less than 5%, check to see if it's less than 3%, which will result in Low. Finally, return Medium (since C14 wasn't equal to 0 and C18 is greater than or equal to 3% and less than or equal to 5%.

This is perfect, thanks so much for the help Tim - worked a treat! Thanks also for the explanation, it's good to know that the logic behind it all as well, that's really helpful. — MarkS, Nov 30 '15 at 09:36

score 0 · Answer 3 · answered Nov 27 '15 at 17:37

You have a typo in the last IF. I believe you meant C18 not C14.

Anyway, that last IF will never execute because if C18 = 0, it <3% so it will be assigned Low. The formula is a lot easier to write (and read) if you follow the progression laid out in your business logic

=IF(C18=0,"Null",IF(C18<3%,"Low",IF(C18<=5%,"Medium","High")))

score 0 · Answer 4 · edited May 23 '17 at 11:59

0

Excel won't return NULL as a value. See the SO Answer

However, you could write it up so it displays N/A.

=IF(C18=0,NA(),IF(C18>5%,"High",IF(C18<3%,"Low",IF(AND(C18>=3%,C18<=5%),"Medium"))))

edited May 23 '17 at 11:59

Community

1
1

answered Nov 27 '15 at 17:52

Ian

738
5
13

Multiple IF formula is throwing an error

4 Answers4