Nested IFs are one of those "must knows" and I couldn't believe that I had not written about it before.
A nested IF is an IF statement that has been placed within another IF. This is very handy if you want to test more than 2 conditions.
In Excel 2003, you can have a maximum of 7 levels of IF statements within an If statement although I have had people tell me they have created nested IFs with more than 7 levels using range names. In Excel 2007, you can have up to 64 levels. Before we go any further, let me just say, that if you are anywhere over 7, you seriously need to learn about VLOOKUPs and INDEX MATCH functions. Honestly, no one wants to try to untangle or edit a 64 level IF statement.
Let's use this scenario: A bike company is offering some promotional tours and is trying to keep track of them. There are 3 alternative statuses- the tour is underbooked, overbooked or full.
To create a nested IF with three conditions……………
Start the IF as you normally would and enter the test/condition and the True argument. In the example, I clicked in E14 and then clicked on the fx button to get the IF dialog box.
I typed =IF(C14 is greater than D14 as the Test and then clicked in the Value if True section and typed "underbooked"
(the quotations are because it is text)
The current dialog box collapses and is replaced with a brand new IF dialog box. (Don't worry -if you check the formula bar, you will see the earlier part of the formula still exists!)
I have 2 tests left since I have already taken care of the underbooked status.
So, in the Logical Test, I tested C14=D14 and in the Value if true section typed "Full".
"Overbooked" goes in the Value if False by default since that is the only option left.