Using Nested If Statements in Excel

When writing a macro in Excel you can incorporate an "if then else" statement: IF (something is true) THEN (do something) ELSE (do something else)

You can't do this in a simple cell formula, but there is a way round it. You can use several IF functions together: nested IFs.

Excel - nested ifs

A basic IF function takes the format

IF(logical_test,value_if_true,value_if_false)

for example, if you had a temperature reading in cell B2 and you wanted to highlight when it was too hot, you might use

=IF(B2>35,"TOO HOT","ok")

Excel displays "TOO HOT" if the reading is over 35 degress and "ok" if it is 35 degrees or under.

You could also add a warning option to show when the temperature was getting too warm, for example.

IF(B2>35,"TOO HOT",IF(B2>30,"warning - hot",IF(B2<=30,"ok")))

Here we have put another IF statement into the value_if_false postition. So if the temperature is over 35 degrees Excel goes no further and writes "TOO HOT". If not it goes onto the next statement. If this is not true it carries on until it finds a statement that is.

You can continue in this way with up to 7 IF statements. So in our example you could add "too cold" warnings as well.

=IF(B2>35,"TOO HOT",IF(B2>30,"warning - hot",IF(B2>5,"ok",IF(B2>0,"warning - cool",IF(B2<=0,"TOO COLD")))))


Comments