EXCEL IF FORMULA NESTING

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

IF FORMULA

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 TRUE or FALSE. This is certainly the most important aspect of this Function. More often than not, the use of the IF Function is reserved for Level 2 in Excel and beyond. It is however our belief, that it should also be a part of Level 1 as its use is so versatile, but more importantly its structure is an excellent introduction into the logic of Excel and formulas.

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

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 MAXFunction 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 IFFunction itself.

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 IFFunction. 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 Fx Sign To The Left Of Your Formula Bar Until The Words 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. Meet your class deadline and get a good grade by paying a professional to write your English essay
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.