 |
|
|
|
Download the associated Workbook for this lesson
Now that you have reached the level of advanced Excel you are no doubt not content with applying just simple or even mildly complicated formulas. Gone are the days that you were impressed with Excels ability to return the result from a simple SUM function! By now you are probably ready to face the challenge of being able to get virtually any result from any data, and so you should be! In this, the first lesson in our Excel Level 3 course, we will teach you just how you can easily write those mega functions that you have seen elsewhere.
As with all our lessons we are not satisfied with simply 'showing' advanced formulas, we will teach you how to understand them. By taking this approach you will be armed with the knowledge and confidence to tackle almost any problem.
As using Excel to build advanced formulas will most likely require that we use what is known as Nesting, it is vital that we first fully understand what Nesting is!
Nesting means to use the result of one formula as the argument in another. For instance you may want count all the cells in the range A1:A5 that are equal to the Sum of range B1:B5, so you could use:
=COUNTIF(A1:A5,SUM(B1:B5))
In this case we have used the result of the SUM function as our second argument in the COUNTIF function, so we have nested the SUM function within the COUNTIF function. We can nest a function within any function that takes an argument, but the result of that function must return the same type of result the argument uses. In other words we could not nest a function that only returns Text into the argument of another function that must have a number.
It is very important that we understand this as a lot of Excel's functions will only accept either Text or Numbers, not both. Now, some of you are no doubt saying right now, "The COUNTIF accepts Text and Numbers!" and you are dead right. This means that we could, if we wanted, nest a function into our COUNTIF function that returns Text.
Let's use a small example.
In cell A1 type the word Direction
In cell B1 type the word Names
In cell C1 type the word Age
In cell D1 type the word Sex
In cells A2:A5, type in North, South, East, West
In cells B2:B5, type in Dave, Bill, Mary, Fred
In cells C2:C5, type in 22, 33, 19, 45
In cells D2:D5, type in Male, Male, Female, Male
Now place this formula in any blank cell.
=COUNTIF(B1:B5,VLOOKUP("North",A1:B5,2,FALSE))
In the above formula we are returning the count of occurrences of the data that is on the same row as the word North (in column A), but in column B (Dave). So this proves that we can nest a function into the criteria argument of the COUNTIF function that returns either Text or a Numeric value. You should have a value of 1 as there is only one occurrence of the name Dave in the table.
But lets now assume we need to not only supply the criteria argument, via another function, but also the range argument for the COUNTIF function. For example suppose our range argument is being supplied from another cell that the user types into. Let's make that cell F8, and in that cell we have the Text B1:B5. You may consider using:
=COUNTIF(F8,VLOOKUP("North",A1:B5,2,FALSE))
But this will only result in zero because all we have done is told Excel to count the occurrences of the VLOOKUP result in cell F8! We need to actually force Excel to see the content of cell F8. On top of this we also need to force Excel to see the content of cell F8 as a range address, not simply as a Text string! To achieve this we need to use the INDIRECT function.
According to Excel's help:
INDIRECT Returns the reference specified by a text string. References are immediately evaluated to display their contents. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.
End of Excel help
So, following this logic we could use:
=COUNTIF(INDIRECT(F8),VLOOKUP("North",A1:B5,2,FALSE))
Excel would then see the content of cell F8 (B1:B5) as a range address, not a Text string. You should have the result of 1.
Let's now assume that we are not certain in which relative column we need to look in for the VLOOKUP. In the above examples we have used a fixed value of 2 but let's say we only know that the relative column to look in has a heading of Names. For this we could use the MATCH function and nest it into the Col_index_num argument of the VLOOKUP.
In case your not sure about the MATCH function:
According to Excel's help:
Returns the relative position of an item in an array that matches a specified value in a specified order. Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself.
End of Excel help
So, now we know this we could use:
=COUNTIF(INDIRECT(F8),VLOOKUP("North",A1:B5,MATCH("Names",1:1,0),FALSE))
In other words we look in row 1 for our heading Names and MATCH will return the relative position for us. Note that we have also used 0 (zero) as our optional match_type argument. Again if you are not familiar with the MATCH function:
From Excel's help
If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.
If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order.
If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A,...2, 1, 0, -1, -2,..., and so on.
If match_type is omitted, it is assumed to be 1.
End of Excel help
This will of course work fine as we are telling VLOOKUP to look in A1:B5 and MATCH is telling our VLOOKUP to return the result from column 2 (B). Your result should be 1. But what happens if the heading Names is in column 3 (C1), lets find out by swapping columns B and D around. Before we do though place a single ' (apostrophe) in front of our formula like below:
'=COUNTIF(INDIRECT(F8),VLOOKUP("North",A1:B5,MATCH("Names",1:1,0),FALSE))
This will stop our formula from tracking our columns when we cut and move them. So go ahead move the columns so the column headings are:
Column A - Direction
Column B - Sex
Column C - Age
Column D - Names
Now remove the single ' (apostrophe) in front of your formula and you should get 0. The VLOOKUP Function itself though would actually return a #REF error. But as it is nested within the COUNTIF Function it evaluates to 0. This is because our MATCH is telling VLOOKUP to look in the fourth column of A1:B5 and that's just not possible! What we need to do is somehow tell VLOOKUP that the range to look in is A1:D5. Here is how this can be done:
=COUNTIF(INDIRECT(F8),VLOOKUP("North",INDIRECT("$A$1:"&ADDRESS(5,MATCH("Names",1:1,0))),MATCH("Names",1:1,0),FALSE))
You will of course also need to change cell F8 from B1:B5 to D1:D5. In this case we have used the ADDRESS function to return the address of the fifth row (5) and the fourth column: (MATCH("Names",1:1,0)) we have joined this with the Text "$A$1:" using the & (ampersand). We have then nested the result of this ("$A$1:$D5") within the INDIRECT function! This is what we be known as a deeply nested function. There is also a limit to the amount of levels we can nest functions within each other and that limit is seven. You should have a result of 1.
There are of course times (maybe even now) that you need to see exactly how a formula is obtaining its result. There is a relatively simple way to do this:
Click in the cell that is housing your formula
Click the Fx symbol to the left of the Formula bar
This will display the Insert function dialog box, with the COUNTIF function displayed.
Now simply step through each function and it's arguments by clicking the appropriate part in the formula bar.
This method is a very efficient way to break a mega formula into 'bite size chunks'.