Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Nesting IF Worksheet Functions.

Nesting IF Worksheet Functions

by Allen Wyatt
(last updated December 8, 2015)

8

If you have done any programming, you know that you can create conditional statements known as If...Then statements. Basically, these statements give you a way to say "If this is true, then do that." Excel provides an IF worksheet function to accomplish essentially the same thing in a formula.

You already know how to use the IF function because it was covered in other ExcelTips. What you may not know is that you can nest IF functions. For instance, consider the situation where you want to return a value if two other conditions are true. If cell B5 contains a temperature setting and cell B6 contains a pressure setting, you may want cell B7 to contain the words "TOO HIGH" if B5 is greater than 100 and cell B6 is greater than 50. To do something like this, you could use the following formula:

=IF(B5>100,IF(B6>50,"TOO HIGH",""),"")

Notice that there are two IF functions here. The first one checks to see if the value of B5 is greater than 100. If it is, then the next IF function is invoked. This one checks to see if B6 is greater than 50. If it is, then the words "TOO HIGH" are displayed in the cell. If either of the conditional statements fail, then nothing is displayed in the cell.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12611) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Nesting IF Worksheet Functions.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Converting Tables to Charts

Put numeric information in a table and you can then convert that information to a graphical chart using Microsoft Graph ...

Discover More

Continuing Macro Lines

Program a macro, and you can easily find that some lines get very long. If you want to shorten the lines so they are more ...

Discover More

Doubling Your Money

Make your money last longer by using your head when printing labels. Here's a great example of how you can double the usage ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

MORE EXCELTIPS (RIBBON)

Counting Displayed Cells

When you filter data, Excel displays only a portion of what is really in a worksheet. If you want to count the number of ...

Discover More

Using COUNTIF with Colors

Excel allows you to easily format cells with different fonts, borders, and colors. If you want to count the number of cells ...

Discover More

Returning Blanks with VLOOKUP

Normally the VLOOKUP function returns a value, and if it can't return a value it returns a zero. Here's how you can use the ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments for this tip:

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 7 + 8?

2015-12-08 10:25:30

Michael

When I nest IFs, I use Alt-Enter to embed carriage returns in the formula so it can be indented/outlined. This makes it more readable, especially when there are a higher number of nests.

=IF(B5>100,
IF(B6>50,
"TOO HIGH",
""),
"")


2013-09-26 12:39:27

Bryan

Carlos, it seems I didn't understand the question, but it also seems you don't need an answer.

However, I still stand by the only part of my statement you didn't quote: "You really would be better served getting comfortable with the formula bar. The formula selector is nice if you are a really basic user needing really basic functions, but it is limiting if you want to use even a slight fraction of Excel's power."

I played around with the method described in the article you linked; it's nice, but in the time it takes to make one formula I could write ten by hand. If you decide to go back and edit that formula it would be exponentially easier to type the changes than try and manipulate the formula box (depending, of course, on what kind of changes you are making).


2013-09-25 11:36:43

Carlos Mario

"Carlos, I assume by the Name box you mean the formula selector."

No: I mean the box that is to the left of the Function Wizard.

"You can't use this to nest functions, at least not the way you would want to."

Yes, I can: when you type a function name, the Name box (which normally shows the active cell address) turns into a "Function box" in which you can choose a previously used function or another function (with More Functions option)

"You can only use the formula selector to choose the outer-most function, and then you have to manually type the inner functions."

No: when I need to nest functions, I choose the first function from the wizard and the nested (inner) functions from the Name Box.

I found a very good article that describes very well what I am you talking about. Please see:

http://mauriceausum.wordpress.com/2011/10/29/excel-use-the-function-wizard-wisely/

Sincerelly yours,

Carlos Mario


2013-09-25 07:32:43

Barry Fitzpatrick

Shreepad what you say is correct but you have effectively changed the requirement.

More than a few levels of nesting become very messy. In Shreepad's example I would use the "CHOOSE" function for clarity and ease of use. But I do appreciate that you lose flexibility in the criteria that can be used.

=CHOOSE(A1,"1", "2", "3","4", "5", "6", "7", "8", "9")
values in A1 of less than 1 or more than 10 will result in the "#Value!" result. This can be trapped using an IF function to wrap around the CHOOSE function.

=IF(OR(A1<0,A1>=10),"Try something Else", CHOOSE(A1,"1", "2", "3","4", "5", "6", "7", "8", "9"))

But as ever it all depends on the Users requirement.


2013-09-24 08:31:57

Shreepad S M Gandhi

Barry friend, your alternative is good but true only for the above example. Paste the formula below in any cell other than A1. This is just another example to show how nesting can be put to use.
=IF(A1=0,"Zero",IF(A1=1,"One",IF(A1=2,"Two",IF(A1=3,"Three",IF(A1=4,"Four",IF(A1=5,"Five",IF(A1=6,"Six",IF(A1=7,"Seven",IF(A1=8,"Eight",IF(A1=9,"Nine","Try something else"))))))))))


2013-09-22 07:58:37

Bryan

Carlos, I assume by the Name box you mean the formula selector. You can't use this to nest functions, at least not the way you would want to. You can only use the formula selector to choose the outer-most function, and then you have to manually type the inner functions.

You really would be better served getting comfortable with the formula bar. The formula selector is nice if you are a really basic user needing really basic functions, but it is limiting if you want to use even a slight fraction of Excel's power.


2013-09-21 14:37:53

Carlos Mario

It will be very good if you post an example indicating how to next functions using the Name box, instead of nesting in the Formula Bar.

I think this will be more clear for everyone if you post it in a video.

Thanks,

Carlos


2013-05-04 05:11:23

Barry Fitzpatrick

Another way to achieve the same result is to use the AND function.

=IF(AND(B5>100,B6>50),"TOO HIGH","")

I appreciate the example above is to demonstrate how IF functions can be nested.


Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.

Links and Sharing
Share