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.
- In this example, 20000 is the highest amount that we’re testing for, so that test comes first.
- Next, we test for the lower amount, 10000, and any value that is over that amount is rated as “Average”.
- 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.
____________
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.
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?
You can use the N function to add a note to a formula:
http://blog.contextures.com/archives/2011/01/23/30-excel-functions-in-30-days-22-n/
Or, put line breaks in the formula, by using Alt+Enter