# NESTED if Formula

#### Corleone

##### Well-known Member
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

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

#### Zakkaroo

##### Active Member
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

##### Board Regularmmmm Pizza
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

##### Well-known Member
Sure does

many thanks for solving this

Replies
2
Views
72
Replies
3
Views
46
Replies
4
Views
43
Replies
4
Views
60
Replies
10
Views
114