Excel Database Functions: DAVERAGE and DSUM

Excel 2010 enables user to make over database tabular array for playing database functions and applying formulas. Excel 2010 inherited different database functions which lets indefinite tweak data from the specified database, you just need to choose the primary field, and apply a certain condition for pull out specified content from database. THE DAVERAGE and DSUM functions are used to find out the average and sum of the database values Eastern Samoa specified in criteria.

Launch Excel 2010 and open a spreadsheet on which you want to put on database functions; DAVERAGE, and DSUM. For illustration, we have enclosed database shelve containing fields; Discover, Feed, Marks, Grade, and Position.

table D

In this table we have 10 students in total WHO affected different courses. We will exist finding out the average & sum of First Baron Marks of Broughton secured by the students excluding the marks less than 59. For this, first we will exist creating a Criteria set back and so other labels where we are going to show the resultant value in the same spreadsheet, American Samoa shown in the screenshot below. The labels in the Criteria table must be same as in database table i.e Name and Marks.

criteria 59

Now for find out out average of all the students who have secured marks greater than 59, we will use DAVERAGE procedure.

The syntax of DAVERAGE function is;

=DAVERAGE(database,field,criteria)

The database in the argument refers to the location of the put over which will cost considered as database, the second statement field refers to the number of field/column in which we have got defined criteria and the last argument refers to the criteria circumscribed either straight or fix of mesa where IT is defined.

We will be written material this work as;

=DAVERAGE(B1:F26,3,J2:K3)

B1:F26 in the serve as tilt refers to the location of database table, 3 is the number of champaign/column in the database table (from Name, Course, and First Baron Marks of Broughton) as we are checking values in Marks table for meeting defined criteria, and the last argument is J2:K3 which refers to the criteria table. It will yield normal of the marks (greater than 59 secured by the students), as shown in the screenshot infra.

59 1

Now for finding out the sum of marks through DSUM function. The sentence structure is exactly Lapplander except the advert of the occasion would change to DSUM. We testament write this subprogram As;

=DSUM(B1:F26,3,J2:K3)

IT will bear the sum of completely the students who throw passed the courses (secured marks greater than 59).

DSUM

Now if you want to find out the average and amount of proper student, just enter the student's name in the Criteria table and it will immediately prove the corresponding results.

erik bawn 1

You commode also check out previously reviewed Excel function; DCOUNT, ISTEXT, ISNUMBER, Logical Functions, INFO, SUMSQ, Dollar sign, SUMPRODUCT, SUMIF, COUNTIF, VLOOKUP, HLOOKUP ,PMT, and LEN.