NESTED if Formula

[Corleone]

im trying to get the formula below to give me the desired output
where if the figure in the referenced cell is a negative number
it populated the cell with "Critical"

if its less than 10 but greated than -1 it returns "Warning"

and if > than 10 it returns "OK"

The formula below only seems to recognise 2 of the criteria ie when the cell is a minus number it return "Warning" instead of critical.

=IF(LOCs!J4>10,"OK",IF(LOCs!J4<10,"Warning",IF(LOCs!J4<0,"Critical"))

any help appreciated

 

[Zakkaroo]

im trying to get the formula below to give me the desired output
where if the figure in the referenced cell is a negative number
it populated the cell with "Critical"

if its less than 10 but greated than -1 it returns "Warning"

and if > than 10 it returns "OK"

The formula below only seems to recognise 2 of the criteria ie when the cell is a minus number it return "Warning" instead of critical.

=IF(LOCs!J4>10,"OK",IF(LOCs!J4<10,"Warning",IF(LOCs!J4<0,"Critical"))

any help appreciated

The problem is the ORDER that you've placed the IF statements.

Example: Imagine your number is -1. Your formula hits the first IF statement and says "is it over 10? No! Ok, move onto the next if statement. Is it less than 10? YES! Return Warning"

You need to put the Less than 0 statement in front of the less than 10 statement then it should work.

Hope this helps

 

[Sandeep Warrier]

Your condition of < 0 is after the < 10 condition. Whatever is less than 0 is always less than 10, and so the formula will execute only the 2nd IF.

Try

=IF(A1<0,"Critical",IF(A1<10,"Warning","OK"))

 

[Corleone]

Sure does

many thanks for solving this