PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

Contents

EXCEL DATABASE FUNCTIONS- DAVERAGE

INTRODUCTION

EXCEL provides a complete set of functions to deal with the database data.

DATABASE data has the first row as headers.

DAVERAGE FUNCTION AVERAGES THE VALUES IN A COLUMN [ FIELD ] WHICH FULFILLS A GIVEN CONDITION.

DAVERAGE function is very useful if we need to find out the average of data on the basis of any classification which can be changed by changing the condition.

PURPOSE OF DAVERAGE IN EXCEL

DSUM FUNCTION AVERAGES THE NUMBERS PRESENT IN A COLUMN [ FIELD ] WHICH SATISFIES THE GIVEN CONDITION.

PREREQUISITES TO LEARN DAVERAGE

THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.

  • Basic understanding of how to use a formula or function.
  •  Of course, Excel software.
Helpful links for the prerequisites mentioned above

SYNTAX: DAVERAGE FUNCTION

The Syntax for the DSUM function is

=DAVERAGE(database or range, column or field, Criteria )

The DAVERAGE function syntax has the following arguments:

  • Database    Database is the data on which we want to use the DAVERAGE function. The data is specifically arranged in DATABASE STYLE with LABELS in the top row and data in the lower rows. The range mentioning the database is given including the cells containing the Labels.

  • Field   Column or field is the column on which the criteria will be applied. The column is given by the column name in the “” or it can be given as the column number of the database such as 1 ,2 and so on.

  • Criteria    It is the range which contains the criteria or conditions. It is important that the range contains the column label. It’ll become clearer with the example discussed below.

EXAMPLE: DAVERAGE FUNCTION IN EXCEL

DATA SAMPLE

DAVERAGE FUNCTION usage can be a bit confusing for the new learners. For the simplicity let us take an example to understand the Do’s and Don’t of DAVERAGE.
We have a simple data of a school with number of students in different classes and their attendance.
The table below shows the data present with us.Let us try to find out a few analytical points from the table.
CLASSSTUDENTSATTENDANCE
I4797
II4765
III4071
IV4999
V5091
VI4978
VII4755
VIII4863
IX4988
X4774
XI4054
XII4656
XIII4255
XIV4963
1. Find out the average number of students in each class.
2. Find out the average attendance of the classes with students more than 45.
 
 
daverage excel
EXCEL: DAVERAGE FUNCTION EXAMPLE

1. FINDING OUT THE AVERAGE NUMBER OF STUDENTS IN EVERY CLASS

Let us try to find out the average number of students in every class.
Follow the steps to find out the total sale.
  • After we have our data ready i.e. The table must be having the column labels at the top. [ There is no specific requirements for the column labels. The function would consider the first row as column labels. ]
  • The criteria needs to be declared as a range. The range must contain at least one column label at the top followed by the condition under the columns.
  • Put the formula in a cell where you want the result =DAVERAGE( D5:F19, “STUDENTS” , H5:J6)
  • The function returns the result as 46.42.
  • The following animated picture shows the process of using DAVERAGE function in Excel.
The explanation follows the picture below.
 
daverage excel
STEPS TO FIND AVERAGE USING DAVERAGE FUNCTION IN EXCEL

explanation: finding out THE AVERAGE STUDENTS IN EACH CLASS

The function used for the solution is =DAVERAGE( D5:F19,”STUDENTS “, H5:J6)

Let us analyze the formula used.

The first parameter i.e. D5:F19 is the complete range of the Database [data ].

The second parameter “STUDENTS” is the field or column on which the condition would work and result will be returned. As we want to know the AVERAGE NUMBER OF STUDENTS, this column is needed.

The column is always given in the Double Quotes. [” “].

The last parameter is the range containing the Criteria H5:J6.

Have a look at the way criteria is described. The column names must be same as the database so that the function can lookup and give you the result.

In this case , we didn’t mention anything in the criteria as we want to find the average of all the classes of the school. If we wanted to know the average of any particular classes only, we could mention those classes.

YES, IT IS NECESSARY TO MENTION THE CRITERIA RANGE EVEN IF IT IS BLANK, else the function would return an error.

 

2. FINDING OUT THE AVERAGE ATTENDANCE OF THE CLASSES HAVING MORE THAN 45 STUDENTS

Let us try to find out the total earnings done by Team C.
Follow the steps to find out the total earnings.
  • After we have our data ready i.e. The table must be having the column labels at the top. [ There is no specific requirements for the column labels. The function would consider the first row as column labels. ]
  • The criteria needs to be declared as a range. The range must contain atleast one column label at the top followed by the condition under the columns.
  • Put the formula in a cell where you want the result =DAVERAGE( D5:F19,” ATTENDANCE “, H13:J14)
  • The function returns the result as 1851.
  • The following animated picture shows the process of using DSUM function in Excel.
The explanation follows the picture below.
 
daverage excel
STEPS TO FIND CONDITIONAL AVERAGE USING DAVERAGE FUNCTION IN EXCEL

explanation: finding out THE AVERAGE ATTENDANCE OF THE CLASSES MORE THAN 45 STUDENTS

The function used for the solution is =DAVERAGE( D5:F19 ,” ATTENDANCE “, H13:J14)

Let us analyze the formula used.

The first parameter i.e. D5:F19 is the complete range of the Database [data ].

The second parameter “ATTENDANCE” is the field or column on which the condition would work and the answer would be returned from this column.  As we want to know the average of the attendance, this column is needed.

The column is always given in the Double Quotes. [” “].

The last parameter is the range containing the Criteria H13:J14.

We need to find out the attendance of the classes which have more than 45 students. So we have put the condition under the STUDENT column as >45 which will instruct the function to check only the classes with more than 45 students.

OTHER WAYS TO REACH THIS ARTICLE

  • WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL

LEARN

Encourage us by following at …

If you have any queries or suggestions or any problem which you would like us to solve for you, kindly mail at

[email protected]