Lesson 37 - Excel IF Formula Nesting

Category: [Excel] Demo Available

# EXCEL IF FORMULA NESTING

## Lesson 37 - Excel IF Function/Formula. Using the IF Formula in Excel. Level 1 Free Training

###

### IF FORMULA

### Gallery

Download the associated Workbook for this lesson

The **IF** Function is categorized under the **Logical** category in the **Insert Function** dialog box *(Note: In earlier versions of Excel, this was known as the Paste Function dialog box).* While its uses can vary greatly, the structure of the Function itself is very simple, in that it will return either

**When to Use IF**

The **IF** Function can be used whenever we wish to have the ability to return a particular result that is dependant on another. For example we may want a formula to SUM a range of cells if the value of a particular cell is greater than 100, but if the value of this particular cell is less than 100 we may wish to perform another calculation altogether. This is often referred to as the **What-If** analysis. **What if** this were that value or **what if** this was another value? While the **IF** Function can be used on its own, it is often combined with another Function. This combining of Functions in Excel is what is known as **Nesting**.

**What is Nesting**

The term **nesting** in Excel means using the result of one Function as the argument of another. As you may recall, most (not all) of Excels Functions take what are known as arguments. The **SUM** Function can take up to 30 arguments. These arguments must be number(s), a reference to number(s) or a text value, e. g. "20". The numbers that are used for one or more of these arguments could be derived from the result of another Function, if they were, it could be that we have nested another Function or Functions as the argument for the **SUM**Function.

Lets use a simple example to see how this works. Assume we have two columns of numbers, one column of numbers is within the range A1:A10 and the second column of numbers is within the range B1:B10. Now assume we need to find out the **SUM** of the largest numbers in each of these columns. To do this we could nest two **MAX** Functions (MAX is the Function used to find the largest number in a range) into the **SUM** Function, as shown below.

**=SUM(MAX(A1:A10),MAX(B1:B10))**

What we have done here is nested two **MAX** Functions within the **SUM** Function. The reason it is considered nested is because the result of **MAX(A1:A10)** is used as the first argument of the **SUM** Function and the result of **MAX(B1:B10)** is used as the second argument of the **SUM** Function. The Functions in their entirety makes up a formula!

In case you have forgotten the syntax for the SUM Function is

**SUM(number1**,number2,…. . **)** and up to number30.

So in the above example we have used **MAX(A1:A10)** as **number1** and **MAX(B1:B10)** as number2.

You will probably find the hardest thing about nesting Functions is knowing where to place all the parenthesis. Thankfully we can have the **Insert Function** dialog box do this for us! Lets use the nested **SUM** and **MAX**Function to see this.

- Place The Number1 In Cell
**A1**,**2**In**A2**,**11**In**B1**And**12**In**B2**. - Now Highlight Cells
**A1:B2**And Use The**Fill Handle**To Drag Down To Row**10**. - This Should Give You
**1**To**10**In**A1:A10**And**11**To**20**In**B1:B10**. - Now Select Cell
**C10**And Push**Shift + F3**To Display The**Insert Function**Dialog Box. - Select
**Math & Trig**From**Or Select A Category:**And**SUM**From The**Select A Function:**Box And Click**OK**. - You Will Notice That Excel Has Assumed We Want The Range
**A10:B10**As Our**Number1**Argument. This Is Wrong In This Case, So Delete It. - If You Look To The Immediate Left Of The
**Formula Bar**You Will See A Box Where The Name Box Is Usually Placed With**SUM**Written On It And A Drop Arrow On Its Right. Click This Arrow! - You Will See A List Of The Last
**10**Used Functions. Click**More Functions…**And Our**Insert Function**Dialog Box Will Display Again. - Select
**Statistical**From The**Or Select A Cateogry:**And**MAX**From The**Select A Function:**Box And Click**OK**. - Our
**SUM**Function Will Now Have Been Replaced With The**MAX**Function. If You Look In Your Formula Bar You Will See**=SUM(MAX(A10:B10))**. - So Excel Has Already Nested The
**MAX**Function As The First Argument Of The**SUM**Function. Which Is What We Want, But The Range Is Wrong, Simply Delete It. - Click The Collapse Dialog Button On The Right Of The
**Number1**Argument Box And Highlight Range**A1:10**And Then Click The Expand Dialog Button. - Now We Have The Range
**A1:A10**As The First Argument Of The**MAX**Function And The Result Of This MAX Function Is Being Used For The First Argument Of The**SUM**Function. - What We Need To Do Now Is Use Another
**MAX**Function As The Second Argument Of The**SUM**Function. This Means We Have To Activate The SUM Function Again, At The Moment The**MAX**Function Is The Active Function. - To Do This Simply Click On The Word
**SUM**Within The Formula Bar, And You Will See:**=SUM(MAX(A1:A10))**And The**SUM**Function Will Again Be The Active Function. - Click Within The
**Number2**Argument Box And Then To The Left Of The Name Box You Will See The**MAX**Function, Click On This. If It Does Not Say**MAX**Simply Click The Drop Arrow And Select**MAX**. - This Will Place The
**MAX**Function Into The Number2 Argument Of The**SUM**Function And In The Formula Bar You Will See**=SUM(MAX(A1:A10),MAX(A10:B10))**. Delete The**A10:B10**And Then Using The Collapse Dialog Button And Highlight The Range**B1:B10**And Click**OK**.

You should now have the formula: **=SUM(MAX(A1:A10),MAX(B1:B10))** and the result of **30**. This same principle applies to any Functions that we need to nest together.

There are two rules that apply whenever we nest Functions and these are:

- The Function That We Nest Within The Argument Of Another Function Must Return The Data Type Expected By That Argument. This Means We Could Not Use A Function That Only Returned Text As The Argument Of A Function That Can Only Accept Numeric Values.
- We Can Only Nest Functions Up To Seven Levels Deep. This Is Explained Quite Well In The Excel Help Under:
**About Multiple Functions Within Functions, Or Nesting**As It Is What We Have Discussed Above.

By now you are probably asking yourself "what has this to do with the **IF** Function?"We have used the above example as a sort of primer as we have discussed the **SUM** and **MAX** Function before. The other reason is that the **IF** Function very often has other Functions as its arguments, in other words it is very common to nest Functions when using the **IF** Function. But before we do nest another Function within it let’s look at the **IF**Function itself.

**IF**

The IF Function, as mentioned above, can be found under **Logical** in the **Or Select a Category:** area of the **Insert Function** Dialog Box. The syntax of the **IF** Function, is as shown below:

**=IF(logical_test**,value_if_TRUE,value_if_FALSE)

In a nutshell, the **IF** Function returns one value if a chosen condition is **TRUE** and another value if a chosen condition is **FALSE**. As you can see by the syntax, the **IF** Function can take three arguments. But it only requires the **logical_test** argument and at least one of the other two; that is **value_if_TRUE** or **value_if_FALSE**. While it may seem a bit confusing by looking at the syntax for the IF Function, it really is a very simple formula to use and apply once you have a basic understanding of it.

Lets use a small example to demonstrate what I mean.

- In Cell
**A1**Type The Number1. - Click In Any Other Cell And
**Type =IF(A1>0,2)**And Push**Enter**.

You will get the result of 2. The reason why we are getting the result of 2 is simply because our first argument, (**logical_test** which is **A1>0**) is TRUE and so our **IF** Function is evaluating to **TRUE** and so returns the argument for **value_if_TRUE** which is **2**. So in plain English, we are saying **IF** cell **A1** contains a value greater than **0**, return the value **2**. So in this instance, we have used two of the three arguments for the **IF**Function. The next logical question should probably be “what value will be returned if cell **A1** does not have a value greater than 0?” The easiest to see what value it would return is to:

- Type The Value
**–1**In Cell**A1**.

Your IF Function now should be returning the word **FALSE**. The reason it is returning **FALSE**, is simply because our logical test no longer evaluates to **TRUE**, but to **FALSE** and as we have not supplied an argument for the** value_if_FALSE** Excel will by default use the word **FALSE**.

Let’s now go in and edit our **IF** Function and make it return another value other than FALSE. The way we show you how to edit the **IF** Function here, can be used on any Function and is an easy way to Edit Formulas and also troubleshoot them.

- Click In The Cell That Contains The
**IF**Function. - On The Formula Bar, Wave Your Mouse Pointer Over The
Sign To The Left Of Your Formula Bar*Fx**Insert Function*Appear In A Tooltip.*(Note:The***Fx**Sign May Be An = Sign In Earlier Versions Of Excel. If So The Words Edit Formula Will Appear Instead Of Insert Function) - Simply Left Click On The Sign And Excel Will Automatically Display The
**IF**Function Dialog Box. - Using This Dialog Box, We Can Now Type In A Value For Our
**IF**Function To Return If Our Logical Test Is FALSE. Before You Do, If You Look Down The Very Bottom Of This Dialog Box, You Will See The Words**Formula Result = FALSE**. - Type The Number 5 In The
**Value If FALSE**Argument Box And This Should Immediately Change To Say**Formula Result = 5**. It Is Not Necessary For Our Value_if_TRUE Or Our Value_if_FALSE Argument To Return A Numeric Value. We Can, If We Wish, Have It Return Text Or Even An Entire Sentence If We Wanted. - Click In The
**Value_if_TRUE**Argument Box And Type**Yes**. - In The
**Value_if_FALSE**Argument Box Type**No**. - Click The
**OK**Button.

You will now see that by changing the value in **A1** to values less than and greater than **0**, your **IF** Function cell with return either **Yes** or **No** to reflect the change.

This is basically all there is to the IF Function, it will do one thing if a logical test is TRUE and another if it is FALSE. Obviously, the example we have used here would be of no practical value to anybody.

So let us now use a more realistic example and also incorporate what we have learnt about nesting.

- Place The Numbers
**1 - 10**In Cells**A1:A10**. We Will Assume That If The**SUM**Value Of These 10 Numbers Exceeds 100, We Would Like To Return The Actual**SUM**Value Of The Numbers. If On The Other Hand The**SUM**Value Does Not Exceed 100, We Would Like To Return Only The**MAXIMUM**Number Within The Range. - Click In Cell
**A11**, Push**Shift + F3**. - Click
**Logical**Within The**Or Select A Category:**Area**.** - Click
**IF**Within**Select A Function:**And Click**OK**. - Ensure Your Mouse Insertion Point Is Within The
**Logical_test**Argument Box. - Click On The Small Drop Arrow To The Left Of Your Formula Bar. This Is Where The Name Box Would Normally Be.
- As We Have Used The
**SUM**Function Previously, It Should Be Part Of The List Already. But If Not, Simply Click**More Functions**And Locate It From Within The**Math & Trig**Area Under**Or Select A Cateogry:**And Click**OK**. - By Default Excel Should Automatically Have Selected The Range
**A1:A10**For You As The First Argument Of The**SUM**Function. If Not, Click The Collapse Dialog Box Button Highlight The Range**A1:A10**And Click The Expand Dialog Box Button. - If You Now Look In Your Formula Bar You Should Have
**=IF(SUM(A1:A10))**. - Now Click Back On The
**IF**Within The Formula Bar To Activate Our**IF**Function Again, And We Should Have For Our**Logical_test SUM (A1:A10)**. At Present The**Logical_test**Will Be Evaluating To**TRUE**. - Within The
**Logical_test**Argument Box, Click Immediately To The Right Of**SUM(A1:A10)**, So Your Mouse Insertion Point Is Immediately Outside The Closing Parenthesis. - Simply Type
**> 100**. Now Our Logical Test Will Evaluate To**FALSE**. - Click Within The
**Value_if_TRUE**Argument Box And Select The**SUM**Function Again From The Box Immediately To The Left Of Your Formula Bar. Again, By Default, Excel Will Automatically Place**SUM(A1:A10)**So Within Your Formula Bar Now, You Should Have**=IF(SUM(A1:A10)>100,SUM(A1:A10))**. - Again Activate The
**IF**Function By Clicking On The Word**IF**In The Formula Bar And You Will See We Now Have**SUM(A1:A10)**In The**Value_if_TRUE**Argument Box. - Click In The
**Value_if_FALSE**Argument Box, Click The Drop Arrow To The Left Of The Formula Bar And Select**MAX.**If It Is There, It Not Select**More Functions**And Locate It Under The Category**Statistical**. - Again, By Default Excel Should Automatically Use The Range
**A1:A10**As The First Argument For The**MAX**Function. - If You Now Look In The Formula Bar, You Should See
**=IF(SUM(A1:A10)>100,SUM(A1:A10),MAX(A1:A10))**

Looking at the formula like this is certainly not very easy to read, let alone decipher what its intention is. By far the easiest way to find out what it is supposed to do is to again activate the **IF** Function by clicking on the word **IF** in the Formula Bar. Then looking at the structure of the Formula like this, you should see quite clearly what its intention is. Click the **OK** button.

In plain English, you could say that the formula reads:

**If the sum of A1:A10 is greater than 100 return the sum of A1:A10. Otherwise, if not, return the maximum number of A1:A10**. The final result of our formula, of course, is 10. If you now change any one of the numbers within the range **A1:A10** so that the **SUM** value of these numbers is greater than **100**, you will see that our **IF** Function is evaluating to **TRUE** and so returns the **SUM** value of the numbers.

Another very common outcome of the **IF** Function is to use empty text as a result. This is often used in very complex formulas (or what look like very complex formulas) and works like this:

- Click Back In The Cell That Contains The
**IF**Function. - Click In The Formula Bar To The Right Of The Last Bracket And Backspace Out
**MAX(A1:A10))**In Your Formula. - In Its Place, Type In ””, So Your Formula Now Should Read
**=IF(SUM(A1:A10)>100,SUM(A1:A10),””)** - Click
**Enter**. - So The Logic Of Our Formula Now Is
**If The Sum Of A1:A10 Is Greater Than 100 Return The Sum Of A1:A10. Otherwise, Say Nothing.**

Go back to:

See also:

Lesson 38 - Excel Function Now/Today Formulas |

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft.

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.