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")

iffunction02

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"))

nestedif01

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.

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.

 IMG_9211

____________

You may also like...

3 Responses

  1. Yehuda says:

    Hi,
    This is a great tip, as I was looking for how to use IF…OR…, and this IF…IF.. perfectly substitutes it.

    Thank you for sharing the knowledge.

  2. Suwandy says:

    Hi, I would like to ask a question:
    How to put comments in if functions?
    Let’s say that we have a long nesting IF Functions and we would like to separate them with comments to make it easier to read.
    Is it possible?

Leave a Reply to Suwandy Cancel reply

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