CODEWITHSUNDEEP
excelexcel-formula
MarkS
MarkS

Reputation: 3

Multiple IF formula is throwing an error

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.

Upvotes: 0

Views: 76

Answers (4)

Ian
Ian

Reputation: 748

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

Upvotes: 0

Code Different
Code Different

Reputation: 93161

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

Upvotes: 0

pnuts
pnuts

Reputation: 59475

Please try: 

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

but adjust the break points to suit.

Upvotes: 1

Tim
Tim

Reputation: 28530

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

Upvotes: 0

Related Questions