Nested IF Functions

Identify the Critter - a Nested If statement

We could write an If function that can determine what sort of critter we have by counting its legs.

=IF(A2 >6,"SPIDER","not a SPIDER")

This IF function only identifies whether a critter is a spider or not.

However by replacing the "not a SPIDER" with another IF function, IF(A2 =6, "INSECT", "ANIMAL"), we can make a more powerful function.

Place the following function in cell A3.
=IF(A2 >6,"SPIDER",IF(A2 =6, "INSECT", "ANIMAL"))

This If function contains another If function nested inside it.

The function now tests for 3 different values in cell A2.

• number of legs > 6
• number of legs = 6
• number of legs < 6

Now try entering different numbers of legs in cell A2.
(Okay okay, who brought the centipede in and wrecked the model !)

Try rewriting the function to allow for the centipede.

You can see from this example that where many values need to be tested the nested if statements can become extremely complex.

Fortunately we have the Choose and Lookup functions which can handle such complications easily.

But first lets examine compound IF statements.

---