THE IF FUNCTION AND NESTING

LESSON WORKBOOK:  Level 1 Lesson 10 2007.xlsx

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

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:

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.

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:

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.

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.

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:

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.

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 many more 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 the Office Button>Excel Options and selecting Formulas from the left hand side of the dialog.  Under Calculation options on the right are the different modes you can have.  Having said this though be very careful when doing this as you can easily inadvertently feed yourself false information.  Our advice is to only change from automatic calculation if you really need to and then only if you are fully aware of the consequences.

 

 

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

OzGrid.com accepts no responsibility for any adverse affect that may result from undertaking our training.