IF – one IF nested in another IF

There are many situations, when you want to use IF, but need more options than 2. In this example I am going to decide, whether there is positive number, negative number or zero in some cell – which means there are three options.

How to combine multiple IF functions.

IF generally evaluate some statement (condition) and according its meeting provides the first or second value. But it is always yes or no. To have three options, we need multiple IF functions and combine them.

The formula looks like this:

• =IF(A1>0;”positive number in A1″,IF(A1=0;”zero in A1″;”negative number in A1″))

The message for Excel is:

• “If there is something larger than 0 in A1, then type “positive number” and that´s all. Otherwise follow to next evaluation – if there is 0 in A1, type “zero” and if no, type “negative number”.”

Notes.

• Notice that the nested function IF is actually the “inner” argument of the “outer” IF.
• Be careful about brackets
• In old versions of Excel the number of nested IF in one formula is limited to 7
• More about IF is here
• If there is very complex IF function, it can be often replaced by VLOOKUP
• You can often simplify very complex IF function by combinations with AND or OR functions.