Level 3 - Lesson 9 - Array Formula
Category: [General,Excel] Demo Available
Array Formula
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:
- Each argument within an array must have the same amount of rows and columns.
- You must enter an array by pushing Ctrl+Shift+Enter.
- You cannot add the {} (braces) that surround an array formula yourself, pushing Ctrl+Shift+Enter will do this for you.
- 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)
- 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.
- In cell B21 (directly below Dates) place ="=>"&B5
- Directly below this (cell B22) place ="<" &B12
- 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/10/10
- Less than 01/11/10
- 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 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.
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets and Index to Excel VBA Level 1 Free Lessons and
Index to Excel Level 2 Lessons and Index to COVID-19 Charting examples