**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.

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**.

**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!

The data validation capabilities of Excel are really handy when you want to limit what is put into a cell. However, you can't ...

Discover MoreExcel includes a surprising number of functions you can use to round your data. Two such functions are FLOOR and CEILING, ...

Discover MoreNormally 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**FREE SERVICE:** Get tips like this every week in *ExcelTips,* a free productivity newsletter. Enter your address and click "Subscribe."

2015-12-08 10:25:30

Michael

=IF(B5>100,

IF(B6>50,

"TOO HIGH",

""),

"")

2013-09-26 12:39:27

Bryan

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

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

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

=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

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

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

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

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

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.

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

Copyright © 2017 Sharon Parq Associates, Inc.

## Comments