Difference between revisions of "Manuals/calci/DAVERAGE"

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.

References

List of Main Z Functions

Z3 home

@@ Line 1: / Line 1: @@
-= DAVERAGE(database,field,criteria)=
+<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 '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.
-*'field' shows which column is used in the function and
+**DAVERAGE(), returns the average of selected database entries.
-*'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)
+DAVERAGE (Array,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 'Field' argument can be expressed as "Price" (a column name) or 3 (position of column number).
-* A 'column name' in quotes (e.g. "Salary") or 'column number' (e.g. 3, if Salary 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.
@@ 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.
-<!--
-<div id="8SpaceContent" class="zcontent" align="left">
-<u>[javascript:ToggleDiv('divExpCollAsst_4') <font color="#0000ff">Let's see an example</font>]</u>
-<font color="#0000ff"><br /></font>
-[javascript:ToggleDiv('divExpCollAsst_4') <u><font color="#0000ff">C1 C2 C3 C4 </font></u>]
-<font color="#0000ff"><br /></font>
-[javascript:ToggleDiv('divExpCollAsst_4') <u><font color="#0000ff">R1 Stationary Items Count Price Profit</font></u>]
-<font color="#0000ff"><br /></font>
-<font color="#0000ff"><br /></font>
-[javascript:ToggleDiv('divExpCollAsst_4') <u><font color="#0000ff">R2 Pen 10 40 10</font></u>]
-<font color="#0000ff"><br /></font>
-<font color="#0000ff"><br /></font>
-[javascript:ToggleDiv('divExpCollAsst_4') <u><font color="#0000ff">R3 Pencil 20 30 15</font></u>]
-<font color="#0000ff"><br /></font>
-<font color="#0000ff"><br /></font>
-[javascript:ToggleDiv('divExpCollAsst_4') <u><font color="#0000ff">R4 Books 15 75 20</font></u>]
-<font color="#0000ff"><br /></font>
-<font color="#0000ff"><br /></font>
-<font color="#0000ff"><br /></font>
-<font color="#0000ff"><br /></font>
-<font color="#0000ff"><br /></font>
-[javascript:ToggleDiv('divExpCollAsst_4') <u><font color="#0000ff">I.e. =DAVERAGE(C1R1:C4R6,3,C1R1:C4R4) is 48.333(average price of the items)</font></u>]
-</div>
--->
 == Examples ==
-Consider the following table with Stationary Items, Count, Price and Profit.
+Consider the following table using ZCalci with Stationary Items, Quantity, Price and Percentage Profit.
 <div id="4SpaceContent" class="zcontent" align="left">
@@ Line 81: / Line 29: @@
 {| id="TABLE2" class="SpreadSheet blue"
 |- class="even"
-| class="sshl_f      " | Stationary Items
+| class="sshl_f" |
-| class="sshl_f" | Count
+| class="sshl_f" | A
-| class="sshl_f" | Price
+| class="sshl_f" | B
-| class="sshl_f" | Profit
+| 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" | '''Profit'''
+|- class="even"
+| class="sshl_f" | 2
 | class="sshl_f" | Pen
-| class="sshl_f" | &lt;30
+| class="sshl_f" | &gt;10
 | class="sshl_f" |
-| class="ssh1_f" | &gt;10
+| class="ssh1_f" |
+|- class="odd"
+| class="sshl_f" | 3
+| class="sshl_f" | '''Item'''
+| class="sshl_f" | '''Quantity'''
+| class="sshl_f" | '''Price'''
+| class="sshl_f" | '''Profit'''
 |- class="even"
-| class="sshl_f      " | Stationary Items
+| class="sshl_f" | 4
-| class="sshl_f" | Count
-| class="sshl_f" | Price
-| class="sshl_f" | Profit
-|- class="odd"
 | class="sshl_f" | Pen
-| class="sshl_f" | 10
+| class="sshl_f" | 12
 | class="sshl_f" | 40
-| class="sshl_f" | 10
+| class="sshl_f" | 2
-|- class="even"
+|- class="odd"
+| class="sshl_f" | 5
 | class="sshl_f" | Pencil
 | class="sshl_f" | 20
-| class="sshl_f" | 30
+| class="sshl_f" | 20
-| class="sshl_f" | 15
+| class="sshl_f" | 1
-|- class="odd"
+|- class="even"
+| class="sshl_f" | 6
 | class="sshl_f" | Books
-| class="sshl_f" | 15
+| class="sshl_f" | 25
 | class="sshl_f" | 75
-| class="ssh1_f" | 20
+| class="ssh1_f" | 5
-|- class="even"
+|- class="odd"
+| class="sshl_f" | 7
 | class="sshl_f" | Pen
 | class="sshl_f" | 30
-| class="sshl_f" | 50
+| class="sshl_f" | 93.33
-| class="sshl_f" | 48.3333
+| class="sshl_f" | 4
 |}
-<div align="left">[[Image:calci1.gif]]</div></div>
-----
+= 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 ]]

Difference between revisions of "Manuals/calci/DAVERAGE"

Latest revision as of 13:44, 24 August 2018

Contents

Description

Examples

Related Videos

See Also

References

Navigation menu

Search