DAVERAGE

DAVERAGE(database, field, criteria)

Returns the arithmetic mean of all the numbers in a column that satisfies multiple conditions.

databaseThe range of cells that make up the table or list.
fieldThe column name or number indicating which column to use.
criteriaThe range of cells that contain the conditions.

REMARKS
* For an illustrated example refer to the Specifying Database Criteria page.
* Any cells containing logical values, text and errors are not included. See Example 7.
* This function supports wildcards (? and *).
* The "database" range of cells can be a single column. See Example 5.
* The "database" range of cells must contain column headings in the first row.
* If the "database" range refers to a cell inside a pivot table, this will refer to the data currently displayed in the pivot table.
* The "field" can be a column label, a column number (left to right) or a cell reference.
* The "field" label is not case sensitive. See Example 2.
* If "field" is left blank, then #VALUE! is returned.
* If the "field" column contains some text entries, then these are ignored.
* The "criteria" must include at least one column heading and one cell below the column heading.
* The "criteria" must be in a contiguous block of adjacent cells.
* If the "criteria" is on the same sheet, do not put it below the data, in case more data gets added.
* If "criteria" is left blank, then #VALUE! is returned. See Example 18.
* Make sure the "criteria" and "database" ranges do not overlap and that they are always separated by at least one blank row or column.
* To perform an operation on an entire column, enter a blank cell below the column heading in the "criteria" range. See Example 10.
* If no rows match the criteria, then #DIV/0! is returned. See Example 15.
* You can use the AVERAGE function to return the arithmetic mean with no conditions.
* You can use the AVERAGEIFS function to return the arithmetic mean that satisfies multiple conditions.
* This is similar to DCOUNT, DGET, DMAX, DMIN, DPRODUCT, DSTDEV, DSUM and DVAR functions.
* For the Microsoft documentation refer to support.microsoft.com
* For the Google documentation refer to support.google.com

 ABCD
1=DAVERAGE(B1:D10, "Qty", B12:B13) = 19DateQtyCost
2=DAVERAGE(B1:D10, "QTY", B12:B13) = 19Jan1918,000
3=DAVERAGE(B1:D10, 2, B12:B13) = 19Mar2024,000
4=DAVERAGE(B1:D10, "Cost", B12:B14) = 19,000Feb1716,500
5=DAVERAGE(C1:C10, "Qty", C12:C13) = 18.67Jun2220,000
6=DAVERAGE(B1:D10, "Qty", C12:C14) = 19.75 1819,500
7=DAVERAGE(B1:D10, "Cost", C12:D13) = 24,000Nov2124,000
8=DAVERAGE(B1:D10, "Qty", D12:D13) = 21.67Maytext26,500
9=DAVERAGE(B1:D10, "Cost", B12:D14) = 20,000Apr1718,500
10=DAVERAGE(B1:D10, "Cost", D16:D17) = 21,778Mar2429,000
11=DAVERAGE(B1:D10, "Cost", C16:D17) = 23,000   
12=DAVERAGE(B1:D10, "Cost", B19:B20) = 19,000DateQtyCost
13=DAVERAGE(B1:D10, "Date", C12:C13) = #DIV/0!Jan<22>20000
14=DAVERAGE(B1:D10, "Qty", B12:D13) = #DIV/0!Jun>18 
15=DAVERAGE(B1:D10, "text", B12:B13) = #VALUE!   
16=DAVERAGE(B1:D10, "Qty", C12) = #VALUE!DateQtyCost
17=DAVERAGE(B1:D10, "Cost", C19) = #VALUE!Jun>18 
18=DAVERAGE(B1:D10, "Qty", ) = #VALUE!   
19=DAVERAGE(B2:D10, "Qty", B12:B13) = #VALUE!Date  
20=DAVERAGE(B1:D1, "Qty", B12:B14) = #VALUE!J*  

1 - What is the average "Qty" when Date="Jan", using a field label. (19)
2 - What is the average "QTY" when Date="Jan", with the field label in uppercase. (19)
3 - What is the average "Qty" when Date="Jan", using a field number. (19)
4 - What is the average "Cost" when Date="Jan" or "Jun". (18000, 20000)
5 - What is the average "Qty" when Qty<22. This uses a single column. (19, 20, 17, 18, 21, 17)
6 - What is the average "Qty" when Qty<22 OR Qty>18. (all the numbers)
7 - What is the average "Cost" when Qty<22 AND Cost>20,000. The cost for May is not included. (24000, 24000)
8 - What is the average "Qty" when Cost>20,000. (20, 21, 24)
9 - What is the average "Cost" when Date="Jan", Qty<22, Cost>20,000 OR Date="Jun" and Qty>18. (20000)
10 - What is the average "Cost" for all the items. Putting a blank cell below the column heading will refer to the entire column. (all the numbers)
11 - What is the average "Cost" when Qty>18 and Cost has no criteria. (18000, 24000, 20000, 24000, 29000)
12 - What is the average "Cost" for all the dates that start with the letter "J". (18000, 20000)
13 - If the "field" column does not contain any numbers, then #DIV/0! is returned.
14 - If the "criteria" you specify does not return any rows, then #DIV/0! is returned. The Cost for January was only 18,000.
15 - If the "field" does not match one of the column headings, then #VALUE! is returned.
16 - If the "criteria" only includes a column heading, then #VALUE! is returned.
17 - If the "criteria" refers to a single empty cell, then #VALUE! is returned. C19 is empty.
18 - If the "criteria" is left blank, then #VALUE! is returned.
19 - If the "database" range does not include column headings, then #VALUE! is returned.
20 - If the "database" range only includes column headings, then #VALUE! is returned.
© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited Top