Nested IF Function in Excel

Hopefully you have come to this post after reading my previous article about using IF functions in Excel.  This is a next step to using IF in Excel.  Previously I had mentioned you can only have three variables in Excel when using IF – these are; the variable you wish to check, the answer if true and the answer if false.  Well what if you want to check a second or a third variable before returning an answer?  This is where nested IF functions come into play when used in a formula.

Nesting IF Statements
When nesting IF functions you can actually check different variables before moving on to the next one. The example below is a basic nested IF function in an Excel formula.

=IF(B2>C2,"Yes B2 is Greater than C2",IF(B2>D2,"Yes B2 is Greater than D2","B2 is Lower than C2 and D2"))

Now, let’s break it down into plain English to understand what is being asked, and I’ll replace the cell references with the corresponding value as shown in the image.image-1

IF(5>7,"Yes B2 is Greater than C2",
This is the first IF. If 5 was greater than 2 (which it isn’t) then Excel would display “Yes B2 is Greater then C2” as the answer because this is true.  But it isn’t.  So excel moves on to the value if false option – also an IF function, which is the next part detailed below:

IF(5>2,"Yes B2 is Greater than D2","B2 is Lower than C2 and D2"))
So, because Excel got a false answer in the first part it now moves to this IF function.  It’s now asking, if 5 is greater than 2 then the answer to display is “Yes B2 is Greater than D2”, which is correct because the answer is true.  If we had asked, ‘is 5 greater than 9’, then the formula would have displayed “B2 is Lower than C2 and D2” as the answer because it is false – 5 cannot be greater than 9.

Nesting IF functions really increases the power of a basic IF statement.  From memory, I believe you can only nest a maximum of seven IF’s.  But if I am wrong, please do let me know via the comments!  I have said previously, I do not know everything about Excel, and I am looking to learn more as much as I wish to share my knowledge.

Leave a Reply

Your email address will not be published. Required fields are marked *