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

Removing Items from a Context Menu

Context menus appear when you right-click an item in Excel. If you want to modify the menu that appears, the way to do so is ...

Discover More

Documents Opening in the Wrong Program

Double-click a Word document on your desktop, and you expect Word to spring into action and load the document. What if doing ...

Discover More

Locked File Puzzle

What would you do if every time you opened a workbook Excel told you it was locked? Here's how you can try to recover from ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Getting the Name of the Parent Workbook

If you need to insert into a cell the name of the workbook in which a worksheet is contained, you can use the CELL function. ...

Discover More

Exact Matches with DSUM

The DSUM function is very handy when you need to calculate a sum based on data that matches criteria you specify. If you ...

Discover More

Using the INT Worksheet Function

The INT function allows you to convert a value to an integer. The effect the function has depends on the characteristics of ...

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

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. Maximum image size is 6Mpixels. 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 9 - 2?

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.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

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.