Difference between revisions of "Manuals/calci/DAVERAGE"
Jump to navigation
Jump to search
| (23 intermediate revisions by 3 users not shown) | |||
| Line 1: | Line 1: | ||
| − | = DAVERAGE( | + | <div style="font-size:30px">'''DAVERAGE (Array,Field,Criteria)'''</div><br/> |
| − | + | *Where 'Array' is the range of cells that makes up the list or database | |
| − | *Where ' | + | *'Field' shows which column is used in the function and |
| − | + | *'Criteria' is the range of cells that contains the specified criteria. | |
| − | *' | + | **DAVERAGE(), returns the average of selected database entries. |
| − | |||
| − | *' | ||
| − | |||
| − | *DAVERAGE() | ||
| − | |||
== Description == | == Description == | ||
| − | DAVERAGE( | + | DAVERAGE (Array,Field,Criteria) |
It averages the values in a column of a list or database that match the specified condition. | It averages the values in a column of a list or database that match the specified condition. | ||
| − | + | * A 'Field' argument can be expressed as "Price" (a column name) or 3 (position of column number). | |
| − | |||
| − | * A ' | ||
*Criteria can be any range on the 'Calci' with minimum of one column and at least one row indicating the condition. | *Criteria can be any range on the 'Calci' with minimum of one column and at least one row indicating the condition. | ||
| Line 28: | Line 21: | ||
*DCOUNTA() can be used to count the number of non-blank cells from a list of 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 == | == Examples == | ||
| − | Consider the following table with Stationary Items, | + | Consider the following table using ZCalci with Stationary Items, Quantity, Price and Percentage Profit. |
<div id="4SpaceContent" class="zcontent" align="left"> | <div id="4SpaceContent" class="zcontent" align="left"> | ||
| Line 81: | Line 29: | ||
{| id="TABLE2" class="SpreadSheet blue" | {| id="TABLE2" class="SpreadSheet blue" | ||
|- class="even" | |- class="even" | ||
| − | | class="sshl_f" | ''' | + | | class="sshl_f" | |
| − | | class="sshl_f" | ''' | + | | class="sshl_f" | A |
| + | | class="sshl_f" | B | ||
| + | | class="sshl_f" | C | ||
| + | | class="sshl_f" | D | ||
| + | |||
| + | |- class="odd" | ||
| + | | class="sshl_f" | 1 | ||
| + | | class="sshl_f" | '''Item''' | ||
| + | | class="sshl_f" | '''Quantity''' | ||
| class="sshl_f" | '''Price''' | | class="sshl_f" | '''Price''' | ||
| class="sshl_f" | '''Profit''' | | class="sshl_f" | '''Profit''' | ||
| − | |- class=" | + | |- class="even" |
| + | | class="sshl_f" | 2 | ||
| class="sshl_f" | Pen | | class="sshl_f" | Pen | ||
| − | | class="sshl_f" | & | + | | class="sshl_f" | >10 |
| class="sshl_f" | | | class="sshl_f" | | ||
| − | | class="ssh1_f" | | + | | class="ssh1_f" | |
| − | |- class=" | + | |- class="odd" |
| − | | class="sshl_f" | ''' | + | | class="sshl_f" | 3 |
| − | | class="sshl_f" | ''' | + | | class="sshl_f" | '''Item''' |
| + | | class="sshl_f" | '''Quantity''' | ||
| class="sshl_f" | '''Price''' | | class="sshl_f" | '''Price''' | ||
| class="sshl_f" | '''Profit''' | | class="sshl_f" | '''Profit''' | ||
| − | |- class=" | + | |- class="even" |
| + | | class="sshl_f" | 4 | ||
| 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" | | + | | class="sshl_f" | 2 |
| − | |- class=" | + | |- class="odd" |
| + | | class="sshl_f" | 5 | ||
| class="sshl_f" | Pencil | | class="sshl_f" | Pencil | ||
| class="sshl_f" | 20 | | class="sshl_f" | 20 | ||
| − | | class="sshl_f" | | + | | class="sshl_f" | 20 |
| − | | class="sshl_f" | | + | | class="sshl_f" | 1 |
| − | |- class=" | + | |- class="even" |
| + | | class="sshl_f" | 6 | ||
| 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" | | + | | class="ssh1_f" | 5 |
| − | |- class=" | + | |- class="odd" |
| + | | class="sshl_f" | 7 | ||
| class="sshl_f" | Pen | | class="sshl_f" | Pen | ||
| class="sshl_f" | 30 | | class="sshl_f" | 30 | ||
| − | | class="sshl_f" | | + | | class="sshl_f" | 93.33 |
| − | | class="sshl_f" | | + | | class="sshl_f" | 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. <br>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. <br>Returns '''21''' as a result. | ||
| + | |||
| + | = DAVERAGE(A3:D7,"Quantity",A3:D7) : Calculates the average number of all items in the range A3 to D7. <br>Returns '''21.75''' as a result. | ||
| + | |||
| + | = DAVERAGE(A3:D7,4,A3:D7) : Calculates the average of percentage profit of all the items in the range A3 to D7. <br>Returns '''3''' as a result. | ||
| + | |||
| + | == Related Videos == | ||
| + | {{#ev:youtube|-eYn-c8UIjw|280|center|DAVERAGE}} | ||
| + | |||
| + | == See Also == | ||
| + | |||
| + | *[[Manuals/calci/DCOUNT| DCOUNT]] | ||
| + | *[[Manuals/calci/DCOUNTA| DCOUNTA]] | ||
| + | |||
| + | ==References== | ||
| + | |||
| + | *[http://en.wikipedia.org/wiki/Aggregate_function List of Aggregate Functions] | ||
| + | *[http://en.wikipedia.org/wiki/Aggregate_function DAVERAGE] | ||
| + | |||
| + | |||
| + | |||
| + | *[[Z_API_Functions | List of Main Z Functions]] | ||
| + | |||
| + | *[[ Z3 | Z3 home ]] | ||
Latest revision as of 13:44, 24 August 2018
DAVERAGE (Array,Field,Criteria)
- Where 'Array' 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(), returns the average of selected database entries.
Description
DAVERAGE (Array,Field,Criteria)
It averages the values in a column of a list or database that match the specified condition.
- A 'Field' argument can be expressed as "Price" (a column name) or 3 (position of column number).
- 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 using ZCalci with Stationary Items, Quantity, Price and Percentage Profit.
| A | B | C | D | |
| 1 | Item | Quantity | Price | Profit |
| 2 | Pen | >10 | ||
| 3 | Item | Quantity | Price | Profit |
| 4 | Pen | 12 | 40 | 2 |
| 5 | Pencil | 20 | 20 | 1 |
| 6 | Books | 25 | 75 | 5 |
| 7 | 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:D7) : Calculates the average number of all items in the range A3 to D7.
Returns 21.75 as a result.
= DAVERAGE(A3:D7,4,A3:D7) : Calculates the average of percentage profit of all the items in the range A3 to D7.
Returns 3 as a result.
Related Videos
See Also
References