Other Excel Add-In, Templates and Training

Lesson 10

THE IF FUNCTION AND NESTING

Download Workbook for this lesson

In this lesson we thought we would look at one of Excels most useful Functions, the IF Function.

The IF Function is categorised 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 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 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.

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

Two Other Useful Functions

There are two other very useful Functions in Excel that take no arguments at all. These are the TODAY function and the NOW function. The TODAY Function will return the current date, while the NOW Function will return the current date and time. These can be very useful for a spreadsheet that requires having the current date and/or time. These functions are a bit different than most other Excel Functions in two ways.

  • They are what's known as volatile.

  • They take no arguments.

Volatile

When the term volatile is applied to an Excel Function it means that the Function is recalculated whenever Excel calculates. To understand this we need to know how, or rather when, a normal Function in Excel calculates. Most Functions in Excel will recalculate whenever any cell on which they are dependent changes. By this we mean if we have the function =SUM(A1:A10) in a cell and we changed the value of any cell within the range A1:A10 our SUM function will recalculate to reflect the change. If there was another formula in a cell that was referencing B1:B10 then it would not recalculate if we changed a cell within the range A1:A10. A volatile Function on the other hand, will recalculate whenever any formula within the entire Workbook recalculates, regardless of cell references. A workbook will also recalculate whenever we open or save.

No Arguments

As you are now aware, most of Excels Functions take at least one argument and others take up to 30 arguments. The TODAY and the NOW Function can take no arguments at all. What this means to the user is we simply add them to a spreadsheet like:

=TODAY()

=NOW()

In other words we enter them with empty parenthesis. As an alternative to these Functions, if you only need the current date or time then you can these shortcut keys:

Enter the date CTRL+; (SEMICOLON)

Enter the time CTRL+SHIFT+: (COLON)

This will enter the date or time as a static value.  In other words they will not update, unlike TODAY and NOW. If you are creating a spreadsheet, try not to use too many volatile Functions as this can slow down recalculation.  As an alternative, using the NOW Function as an example, you could place the function into a cell somewhere and then reference that cell with a simple reference like: =A1.

We can also control the way Excel calculates by going to Tools>Options and selecting the Calculation tab. Having said this though be very careful when doing this as you can easily inadvertently feed yourself false information. This is particularly true with the option Precision as displayed. My advice is to only change from automatic calculation if you really need to and then only if you are fully aware of the consequences.

Goto Next Lesson