Microsoft Excel DAVERAGE Function
Basic Description
The Excel Daverage function calculates the average of values in a field (column) in a database for selected records, that satisfy user-specified criteria.
The function is very similar to the Excel Averageifs function, which was first introduced in Excel 2007.
The syntax of the Excel Daverage function is :
DAVERAGE( database, field, criteria )
where the arguments are shown in the table below:
| database | - | A range of cells containing the database. The top row of the database should specify the field names. | ||||
| field | - | The field (column) within the database, for which the average is to be calculated.
This can either be a field number, or can be the field name (ie. the header in the top row of the database) encased in quotes (eg. "Area", "Quarter", etc)
| ||||
| criteria | - | A range of cells that contain the criteria, to specify which records should be included in the calculation.
The range can include one or more criteria, which are presented as a field name in one cell and the condition for that field in the cell below.
eg.
|
Wildcards
You can also use the following wildcards in text-related criteria:
? - matches any single character
* - matches any sequence of characters
* - matches any sequence of characters
if you do actually want to find the ? or * character, type the ~ symbol before this character in your search.
eg. the condition "A*e" will match all cells containing a text string beginning with "A" and ending in "e".
The criteria supplied beneath each field heading can be either:
| - | a numeric value (including an integer, decimal, date, time, or logical value) (eg. 10, 01/01/2011, FALSE) | |
| or | ||
| - | a text string (eg. "Text", "Wednesday") | |
| or | ||
| - | an expression (eg. ">2", "<>0") |
Note that the Excel database functions are not case sensitive. So, for example, the criteria ="North" will be satisfied by cells containing the text "North" or "north".
Excel Daverage Function Examples
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Quarter | Area | Sales Rep. | Sales |
| 2 | 1 | North | Jeff | $223,000 |
| 3 | 1 | North | Chris | $125,000 |
| 4 | 1 | South | Carol | $456,000 |
| 5 | 1 | South | Tina | $289,000 |
| 6 | 2 | North | Jeff | $322,000 |
| 7 | 2 | North | Chris | $340,000 |
| 8 | 2 | South | Carol | $198,000 |
| 9 | 2 | South | Tina | $222,000 |
| 10 | 3 | North | Jeff | $310,000 |
| 11 | 3 | North | Chris | $250,000 |
| 12 | 3 | South | Carol | $460,000 |
| 13 | 3 | South | Tina | $395,000 |
| 14 | 4 | North | Jeff | $261,000 |
| 15 | 4 | North | Chris | $389,000 |
| 16 | 4 | South | Carol | $305,000 |
| 17 | 4 | South | Tina | $188,000 |
The following examples are based on the simple database on the right, which stores the sales figures for four sales representatives, over the four quarters of a year.
Example 1
In the example below, the Daverage function is used to calculate the average quarterly sales in quarters 2, 3 and 4, for the Sales Rep. "Tina". The criteria are specified in cells F1 - G2 and the format of the Daverage function is shown in cell F3.
| F | G | |
|---|---|---|
| 1 | Quarter | Sales Rep. |
| 2 | >1 | Tina |
| 3 | =DAVERAGE( A1:D17, "Sales", F1:G2 ) | |
The above Daverage function calculates the average of the values in cells D9, D13 & D17, and therefore returns the value $268,333.
Example 2
In the example below, the Daverage function is used to calculate the average sales in quarter 1, by sales reps in the "South" area.
| F | G | |
|---|---|---|
| 1 | Quarter | Area |
| 2 | 1 | South |
| 3 | =DAVERAGE( A1:D17, "Sales", F1:G2 ) | |
The above Daverage function calculates the average of the values in cells D4 and D5 and so returns the value $372,500.
Note that, in the above two examples, instead of typing in "Sales" for the field argument, we could have simply used the number 4 (to denote the 4th column of the database).
No comments:
Post a Comment