Difference between revisions of "Manuals/calci/DAVERAGE"
| Line 100: | Line 100: | ||
|- class="odd" | |- class="odd" | ||
| class="sshl_f" | Pen | | class="sshl_f" | Pen | ||
| − | | class="sshl_f" | | + | | class="sshl_f" | 12 |
| class="sshl_f" | 40 | | class="sshl_f" | 40 | ||
| class="sshl_f" | 2 | | class="sshl_f" | 2 | ||
| Line 112: | Line 112: | ||
|- class="odd" | |- class="odd" | ||
| class="sshl_f" | Books | | class="sshl_f" | Books | ||
| − | | class="sshl_f" | | + | | class="sshl_f" | 25 |
| class="sshl_f" | 75 | | class="sshl_f" | 75 | ||
| class="ssh1_f" | 5 | | class="ssh1_f" | 5 | ||
Revision as of 15:44, 11 November 2013
DAVERAGE(database,field,criteria)
- Where 'database' is the range of cells that makes up the list or database
- 'field' shows which column is used in the function and
- 'criteria' is the range of cells that contains the specified criteria.
- DAVERAGE() averages the values in a column of a list or database that match the specified criteria.
Description
DAVERAGE(database, field, criteria)
It averages the values in a column of a list or database that match the specified condition.
- The argument 'field' is optional. If not mentioned, the function averages all the values from database that match the criteria.
- A 'column name' in quotes (e.g. "Price") or 'column number' (e.g. 3, if Price is 3rd column) can be used as 'field' argument.
- Criteria can be any range on the 'Calci' with minimum of one column and at least one row indicating the condition.
- The Criteria columns should not overlap the list of data.
- DCOUNT() can be used to count the cells having numbers from a list or database that match a specified criteria.
- DCOUNTA() can be used to count the number of non-blank cells from a list of database that match a specified criteria.
Examples
Consider the following table with Stationary Items, Quantity, Price and Percentage Profit.
| Item | Quantity | Price | Profit |
| Pen | >10 | ||
| Item | Quantity | Price | Profit |
| Pen | 12 | 40 | 2 |
| Pencil | 20 | 20 | 1 |
| Books | 25 | 75 | 5 |
| Pen | 30 | 93.33 | 4 |
= DAVERAGE(A3:D7,"Price",A1:B2) : Calculates the average price of Pens in the range A3 to D7, for the cells with Quantity>10. Returns 66.66499999999999 as a result.
= DAVERAGE(A3:D7,2,A1:B2) : Calculates the average number of Pens in the range A3 to D7, for the cells with Quantity>10. Returns 21 as a result.
= DAVERAGE(A3:D7,"Quantity",A3:B7) : Calculates the average number of all items in the range A3 to D7. Returns 21.75 as a result.
= DAVERAGE(A3:D7,4,A4:B7) : Calculates the average profit of all the items in the range A3 to D7. Returns 3 as a result.