Nesting IF Functions in Excel

The IF function lets you test something in Excel, and show one result if the test result is TRUE, and another result for FALSE. For example:

=IF(“Debra met Excel MVPs last week”,”Show Picture”,”No Picture”)

And yes, I did meet some Excel MVPs last week, and you can see a picture at the end of this article.

Check Cell Value

As another example of the IF function, you can check the value in a cell, and show “Good” if the value is $20,000 or more. For lower amounts, the result is “Poor”.

=IF(B2>20000,”Good”,”Poor”)

Nested IF Functions

You can use more than one IF function in a formula too. Instead of just Good or Poor, you can nest a second IF in the formula, to test for a lower amount – $10,000 – and rate those results as “Average”.

=IF(B2>20000,”Good”,IF(B2>10000,”Average”,”Poor”))

Watch the IF Order

The key to nested IF functions is to put the tests in order of difficulty.

1. In this example, 20000 is the highest amount that we’re testing for, so that test comes first.
2. Next, we test for the lower amount, 10000, and any value that is over that amount is rated as “Average”.
3. Finally, any amount that fails both tests is rated as “Poor”.

=IF(B2>20000,”Good”,IF(B2>10000,”Average”,“Poor”))

Meeting at Microsoft Canada

Last week, I attended an MVP Open Day at Microsoft Canada, and enjoyed spending time with fellow Excel MVPs, Vittorio Covato, Domenic Tamburino, and Eduardo Pineiro.

They’re all formula experts, who share their Excel expertise in the online forums. “IF” you’ve asked a formula question online, there’s a good chance that they’ve helped you with a solution.

Excel MVPs

In the photo below, you can see Vittorio and Domenic, who kindly allowed me to share this picture with you. Okay, I might have strong-armed them into agreeing, but I thought that you’d like to meet them too.

____________