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

Reversing Print Order

When you print a document, does it come out of the printer in the order you need? Here's how to reverse the print order to ...

Discover More

Keep Your Headings in View

When working with lots of data rows, it is easy to forget what the column headings say. Here's how to keep those headings ...

Discover More

Getting Rid of Stubborn Icons

Ever tried to clean-up the icons on your toolbar only to have one that won't go away? This tip explores some possible ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Using GEOMEAN with a Large List

When performing a statistical analysis on a large dataset, you may want to use GEOMEAN to figure out the geometric mean of ...

Discover More

Converting Radians to Degrees

When applying trigonometry to the values in a worksheet, you may need to convert radians to degrees. This is done by using ...

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. 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 + 4?

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.