DAVERAGE function in Excel

What is DAVERAGE Function in Excel?

The DAVERAGE function is one of the Database functions of Excel. It averages the values of a column in a list or database that matches the conditions you specify.

We can find this function in the Database of the insert function Tab.

How to use DAVERAGE function in excel

  1. Click on an empty cell (like F5 )
Click on an empty cell

2. Click on the fx icon (or press shift+F3)

Click on the fx icon

3. In insert function tab you will see all functions

insert function tab

4. Select Database category

5. Select DAVERAGE function

6. Then select ok

7. In function arguments Tab you will see DAVERAGE function

8. In the Database section you can enter the range of cells that makes up the list or database. A database is a list of related data (ex:Table1)

9. The Field section is either the label of the column in double quotation marks or a number that represents the column’s position in the list (ex:C1)

10. Criteria section is the range of cells that contains the conditions you specify. The range includes a column label and one cell below the label for a condition (ex:Table1)

11. You will see the result in formula result section

How to use DAVERAGE function in excel
DAVERAGE (Table1[#All],C1,Table1[#All])----->>>>answer is  215.4

Examples of DAVERAGE function in excel

Example1:

calculate the average of student ages with DAVERAGE function in excel

 calculate the sum of student ages with DAVERAGE function in excel
DAVERAGE (Table1[#All],C1,Table1[#All])----->>>>answer is  25

Example 2:

calculate the average of student height with DAVERAGE function in excel

 calculate the sum of student height
DAVERAGE (Table1[#All],C1,Table1[#All])----->>>>answer is  201.5

Example 3:

Python code for DAVERAGE function

NameAgeHeightWeight
Olivia25210180
Noah25205235
Oliver27195205
Elijah22198185
James29199231
William29201240
Benjamin21202235
Lucas25200238
Henry22204190

import pandas as pd

df=pd.read_csv(‘example.csv’)

avgdf = df.mean(axis=0, numeric_only= True)

print (avgdf)

What is the purpose of DAVERAGE function?

It averages the values of a column in a list or database that matches the conditions you specify.

What is the Return value  of DAVERAGE function?

output of DAVERAGE function just is number.

DAVERAGE (database, field, criteria)=number

How many arguments does DAVERAGE function have?

DAVERAGE(database, field, criteria)

DAVERAGE function has 3 arguments.

  • In the Database section you can enter the range of cells that make up the list or database
  • Field section is either the label of the column in double quotation marks
  • Criteria section is the range of cells that contains the conditions you specify

Which version of excel supports DAVERAGE function?

This function is available for all excel versions (2003-2019)

Errors in DAVERAGE function

If no record matches the criteria, DAVERAGE returns the #DIV/0! error value.

Errors in DAVERAGE function
DAVERAGE (Table13[#All],"Age",A1:A2)=#DIV/0!

Conditions and criteria range in DAVERAGE function

Criteria argument in DAVERAGE function

we can define any range for the criteria argument: it has 2 condition

1. It includes at least one column label

2. It includes at least one cell under the column label.

For example, if the range A1:A2 contains “Name” in A1 and “Benjamin” in A2,

you could define A1=”Name” & A2=”Benjamin”

Dget function find the word of “Benjamin” under “name” column

location of Criteria range in DAVERAGE function

location of Criteria range in DAVERAGE function

Although the criteria range can be located anywhere on the worksheet, do not place the criteria range below the list. If you add more information to the list, the new information is added to the first row below the list. If the row below the list is not blank, Microsoft Excel cannot add the new information.

Make sure that the criteria range does not overlap the list.

To perform an operation on an entire column in a database, enter a blank line below the column labels in the criteria range.

DAVERAGE related functions :

Leave a Reply

Your email address will not be published. Required fields are marked *