google sheets daverage function database field criteria
Use the Google Sheets DAVERAGE function to get the average of numbers in a field of a database, based on one or more criterion. The DAVERAGE function needs three parameters. They are the database, field, and criteria.
Here is a scenario for which you want to use the Google Sheets DAVERAGE function: Say a student takes three mock tests on the same set of subjects. If you wish to know the average score of the student, from the three tests, for a specific subject, then DAVERAGE is an ideal function to use.
Let's look at the format of DAVERAGE:
=DAVERAGE(database, field, criteria)
Start the formula with the function with an equal-to symbol.
DAVERAGE is the name of the function.
Database is the range or array with the data. The first cell of each column of the database should contain a label.
Field is the column, with numeric values, the function uses to calculate the average value.
Criteria is one or more conditions to calculate the average value.
Here is an example of the DAVERAGE function, which will use a sample database:
Student ID General Knowledge Maths English Mock Test Code
10001 92 94.5 92.5 101
10001 90.5 97 91 102
10001 91 96 93 103
10002 83 86 91 101
10002 87 81 87 102
10002 91 81.5 93 103
Two students with student IDs 10001 and 10002 have taken three mock tests on the same set of three subjects. What is the average, say of the maths scores of the three tests for student ID 10001? You can use
the DAVERAGE function in Google Sheets to calculate the average.
First, let's create the criteria range. Copy the labels of the database. Paste it, say in cell G1.
Type 10001 in the cell immediately below the Student ID column of the criteria range.
The criteria range spans from cell G1 to cell K2.
Here is the formula to find out the average score in the maths test:
=DAVERAGE(database, field, criteria)
Assume that the database spans from cell A1 to cell E4.
For the database, specify the range as A1:E4.
For the field, specify the column number as 3. The columns are numbered as 1,2, and so on, starting from the first column.
For the criteria, specify the range as G1:K2.
=DAVERAGE(A1:E4, 3, G1:K2)
The function will return 95.83 as the average score of the student with studen ID 10001 from the three mock tests.
Say you are interested in the average score in maths of the student only if he or she has scored 75 or more at least in one of the three maths tests.
For the above condition, type the greater than symbol, immediately followed by the equal-to symbol, and the number 75, in the cell immediately below the Maths column of the criteria range.
The function will return 95.83, which is the same average as above, because, the student's score in maths, in each of the three mock tests, is greater than 75.
Do take a look at the video tutorial, which gives the steps to use the Google Sheets DAVERAGE function, with examples.