Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation.

Prepared by David Hawley of OzGrid Business Applications.
Specialists in Microsoft Excel and VBA for Excel

Web: www.ozgrid.com
Email: davidh@ozgrid.com

 

Workbook Download
 This is a zipped Excel Workbook to go with this lesson

 

To purchase all 10 lessons and the 5 Workbooks for immediate download, click here

 

MICROSOFT EXCEL LEVEL 3

Lesson 1 - Advanced Formulas

Now that you have reached the level of advanced Excel you are no doubt not content with just applying 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, I will teach you just how you can easily write those mega functions that you have seen elsewhere. But as with all my lessons I'm not satisfied with simply 'showing' advanced formulas, I 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

This means that you are using the result of one formula as the argument in another. For instance you may want count all the cells in the range A1:A10 that are equal to the Sum of range B1:B10, so you could use:

 
=COUNTIF(A1:A10,SUM(B1:B10))

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.

  1. In cell A1 type the word Direction

  2. In cell A2 type the word Names

  3. In cell A3 type the word Age

  4. In cell A4 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:B10,VLOOKUP("North",A1:B10,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 let's 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:B10". You may consider using:

=COUNTIF(F8,VLOOKUP("North",A1:B10,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 cell F8 content 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.

So, following this logic we could use:

=COUNTIF(INDIRECT(F8),VLOOKUP("North",A1:B10,2,FALSE))

Excel would then see the content of cell F8 ("B1:B10") as a range address, not a Text string. You should have the result of 1.

Let's now assume that we are not certain 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.

So, now we know this we could use:

=COUNTIF(INDIRECT(F8),VLOOKUP("North",A1:B10,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

This will of course work fine as we are telling VLOOKUP to look in A1:B10 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:B10,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:B10 and that's just not possible! What we need to do is somehow tell VLOOKUP that the range to look in is A1:D10. Here is how this can be done:

=COUNTIF(INDIRECT(F8),VLOOKUP("North",INDIRECT ("$A$1:"&ADDRESS(10,MATCH("Names",1:1,0))),MATCH("Names",1:1,0),FALSE))

You will of course also need to change cell F8 from B1:B10 to D1:D10. In this case we have used the ADDRESS function to return the address of the tenth row (10) 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:$D10") 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 = (equal sign) to the right of the Formula bar 

  3. This will display the Paste 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 though I would like to stress that array formulas should not be used unless totally necessary! I say this because I constantly see Workbooks from Excel users that are complaining about how slow their Workbook is in opening, saving, closing and recalculating. This is all too often due to the fact they have loaded their Workbook up with array formulas! If you have not already, then please read here: Array Formulas. I also highly recommend reading the excellent page written here by Bob Umlas But again I will stress that too many will cause problems.

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

There are also 4 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.

I will not say anymore on arrays as the 2 links above will cover them in good detail. The other reason is that I believe there is a much better method that can be used in place of array formulas, and these are discussed below:

Dfunctions

Excel includes twelve of the Database functions and they are referred to as the "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. I 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 the 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. Call up the attatched workbook (AdvancedFormulasL1L3) which contains a spreadsheet that we can work with.

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 below the copied heading Percent Paid and format this cell as a percentage.  Let's also name the table range "AllData". Now select range A20:F21 and give it the name: "Citeria" Now in any cell put:

=DCOUNT(AllData,"Percent Paid",Criteria)

This should give you the result of 3. No big deal here I hear you say! Quite right, we could of course 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 return 2, as there is only two people who have paid 100%  and also had a Full Cost of $65.00. Now that's something that the COUNTIF couldn't do.

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 (in theory) have up to 256 criteria! I say 256 as that is the amount of Columns in an Excel worksheet. Have an array 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. I 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.  

  1. Go to Insert>Name>Define (or Push Ctrl+F3) and then from the Insert name 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 ="<" &B11

  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:

  1. Are equal to or greater than 20/02/01

  2. Less than 28/02/01

  3. Have a full cost of $65.00

  4. Have paid 100%

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 no 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 "Joe H" as he 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 the edit, modify and add to!

I have attached a Workbook that shows various ways these 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 Data>Validation. I find this feature very good when needing to extract data as we can use it to create a "pick from list" in our criteria cells. Should you wish to know more about this feature let me know and we can cover it in more detail.

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.
Prepared by David Hawley of OzGrid Business Applications.
Specialists in Microsoft Excel and VBA for Excel
Web: http://www.ozgrid.com/
Email: davidh@ozgrid.com