Array Formulas

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. Call up the zipped file ExLev3L01.zip and download the 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 (100%) 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: Criteria. Now in any cell put:

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

This should give you the result of 3. 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 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!  We say 256 as that is the amount of columns in an Excel worksheet. Have 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.  

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:

• Are equal to or greater than 20/02/04

• Less than 28/02/04

• Have a full cost of $65.00

• 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 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 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 to edit, modify and add to!

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