NESTED if Formula

Corleone

Well-known Member
Joined
Feb 2, 2003
Messages
714
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
 

Some videos you may like

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
Joined
Jul 6, 2009
Messages
383
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
Joined
Oct 31, 2008
Messages
2,672
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"))
 

Watch MrExcel Video

Forum statistics

Threads
1,109,204
Messages
5,527,405
Members
409,760
Latest member
zeeshansyed

This Week's Hot Topics

Top