ADVANCED FORMULAS

LESSON WORKBOOK:  Level 3 Lesson 1 2007.xlsx

Now that you have reached the level of advanced Excel user, you are no doubt not content with applying just simple or even mildly complicated formulas to your data. 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

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. (Example 1 in the attached workbook)

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. (Example 2 in the attached workbook)

  1. In cell A1 type the word Direction

  2. In cell B1 type the word Names

  3. In cell C1 type the word Age

  4. In cell D1 type the word Sex

  5. In cells A2:A5, type in North, South, East, West

  6. In cells B2:B5, type in Dave, Bill, Mary, Fred

  7. In cells C2:C5, type in 22, 33, 19, 45

  8. 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. (Example 3 in the attached workbook).  Let's make that cell G8, and in that cell we have the Text B1:B5. You may consider using:

=COUNTIF(G8,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 G8! We need to actually force Excel to see the content of cell G8. On top of this we also need to force Excel to see the content of cell G8 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 the following formula (Example 4 in the attached workbook)

=COUNTIF(INDIRECT(G8),VLOOKUP("North",A1:B5,2,FALSE))

Excel would then see the content of cell G8 (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 the following (Example 5 in the attached workbook)

=COUNTIF(INDIRECT(G8),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

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. (Example 6 in the attached workbook) 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: (Example 7 in the attached workbook)

=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 G8 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:

  1. Click in the cell that is housing your formula

  2. Click the Fx symbol to the left of the Formula bar 

  3. This will display the Insert function dialog box, with the COUNTIF function displayed.

  4. 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 chucks'.

Array Formulas

Lets now look at what are known as Array formulas. Before we do we would like to stress that Array formulas should not be used unless totally necessary!  This is because we constantly see workbooks from Excel users who are complaining about how slow their workbooks are in opening, saving, closing and recalculating. This is all too often due to the fact they have loaded their workbook up with Array formulas! 

According to Excel's help:

An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments.

End of Excel help

There are also four fundamental rules that must be adhered to:

  1. Each argument within an array must have the same amount of rows and columns.

  2. You must enter an array by pushing Ctrl+Shift+Enter.

  3. You cannot add the {} (braces) that surround an array formula yourself, pushing Ctrl+Shift+Enter will do this for you.

  4. You cannot use an array formula on an entire column.

If you have not already, then please read here: Array Formulas.  We also highly recommend reading the excellent page written here by Bob Umlas  but again we must stress that too many will cause problems.

There is a much better method that can be used in place of array formulas, and this are discussed below:

Dfunctions

Excel includes twelve Database functions, these are referred to as Dfunctions.  These functions are ideal for extracting and working with data that is in a database, list or table. They are also not used anywhere near enough by most Excel users. We highly recommend learning as much as possible about these functions as they can be invaluable when you need to extract or work with specific data from a database list or table. The help within Excel gives a lot of information on Database Functions and is well worth reading. Although there are twelve of these functions you really only need to know how to use any one of them, as they are all very similar and all have the same syntax.

Dfunction(database,field,criteria) All arguments are mandatory.

database: is simply the range of cells that make up your database, list or table. It must include your headings!

field: is the column within the database, list or table (database) to use in the function. Field can be given as text or the column number of the column in the database, list or table (database) .

criteria: Is a range of cells that specifies the conditions that must be met by the cells within the database to be used in the particular Dfunction. The criteria cells must contain the column heading(s) of column(s) you wish to include in the particular Dfunction.

Let's put this into practice now and see how these can be used. Open the workbook Level 3 Lesson 1 2007.xlsx and click on the Dfunctions tab which contains a data table that we can work with. The table has the range name AllData.

Let's now assume we need to extract from this table of data the number of people that have paid in full (100%). Before we do though, it is good practice to copy the headings only from this table and place them in a convenient place of the Worksheet, these will be used as our criteria. Copy the headings to cell A20 below your table. Type the number 1 (100%) below the copied heading Percent Paid and format this cell as a percentage.   Now select range A20:F21 and give it the name: Criteria. Now in any cell put:

=DCOUNT(AllData,"Percent Paid",Criteria) (Example 8 in the attached workbook)

This should give you the result of 2. No big deal here, we could of course have just used a simple COUNTIF to get that result!

Let's add another criteria then. In cell C21 (directly below Full Cost) type: $65.00. Our function should now be returning 1, as there is only one person who has paid 100%  and also had a Full Cost of $65.00. Now that's something that the COUNTIF couldn't do. (Example 9 in the attached workbook)

This is certainly not the limit of the Dfunctions, in fact we could add a criteria to all our headings if we wished. This would mean we would have a function returning a result that has 6 criteria and if our table had even more columns we could have much more criteria!  Having an Array formula do this a you would end up with a very long (unreadable) formula that would most certainly slow things down!

The above example is the use of one of the Dfunctions in it's simplest forms.  We say this because we can even add formulas for our criteria. But when we do this our formula should result in either True of False.  Try this:  (Example 10 in the attached workbook)

  1. Go to the Formulas tab and under the Defined Names group select Name Manager (or Push Ctrl+F3) and then from the Name Manager dialog change the named range Criteria from $A$20:$F$21 to $A$20:$F$22. Click Add then OK.

  2. In cell B21 (directly below Dates) place ="=>"&B5

  3. Directly below this (cell B22) place ="<" &B12

  4. Now copy down both the $65.00 and the 100% so they are in cells C22 and E22

We have now told our DCOUNT to count only those that:

It is important to note here that we needed to copy down the existing criteria ($65.00 and 100%) as we had expanded our named range Criteria to include another row.  You should have the result of 1.

Let's now try one more example of this by using the DGET function. We will use this to return the name of the person who matches the criteria that we have set above. So in any cell put:

=DGET(AllData,"Name",Criteria)

This should result in Aleisha H as she is the only person that meets this criteria. If they was more than one person that met our condition our DGET would result in the #NUM! error!

Hopefully these examples will convince you that the Dfunctions are a much better choice than an Array formula. Not only are they much faster at recalculating, but they will also not slow down your Workbook and they are also much easier to edit, modify and add to!

On the worksheet called Extracted Information in the attached workbook, we have shown various ways these Dfunctions can be used. Use these examples in conjunction with the help on Database Functions. Some of the examples also use the Validation feature in Excel which is found under the Data Tools group on the Data tab.  This feature is very good when you need to extract data as it can be used to create a "pick from list" in our criteria cells.

Summary

So by using the nesting combination shown above we should be able to  tackle virtually any problem that comes along. We now know that when we nest a function within another function the result of that function must return the data type that the other function accepts. We can also step through a deeply nested function and look at it in "Bite size chunks".

Array formulas, while very handy at times, can become a trap that can lead to a Workbook that is virtually unusable. Before using a array formula, consider whether one of the Dfunctions will do the job. More often than not they can!

The use of Dfunctions are ideal for extracting data from a table of data that need to meet one or more criteria. You can combine this feature with the Validation feature and come up with a very simple method for extracting information. As with most of Excel, once you get to know it quite well, the only limitation is your own imagination.

 

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.