 |
|
|
|
|
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 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:
Go To Free Excel Training Lesson 38 . Back to Previous Lesson
Go to Excel Basic/Level 1 Training Index
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139. 00 of FREE software!
Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
FREE Excel Help
