How to Use Excel's DAVERAGE Function

By eHow Computers Editor

Rate: (1 Ratings)

Excel's DAVERAGE function is short for "database average." It selects a set of values from a column of a database that matches the specified criteria. DAVERAGE then calculates the arithmetic mean of the selected values in the same manner as AVERAGE, the equivalent non-database function. The following steps will show how to use Excel's DAVERAGE function.

Flag Article

Instructions

Difficulty: Moderate

Step1

Learn the syntax of DAVERAGE. It is DAVERAGE(database,field,criteria). Database is a cell range that specifies the database, field is a column in the database from which DAVERAGE will take values and criteria is a cell range containing the criteria to be used in selecting the values.

Step2

Determine which values to consider. DAVERAGE ignores empty cells, logical values and text but includes cells with a zero value. Use the DAVERAGEA function if you wish to include logical values and text representations.

Step3

Expect DAVERAGE to return the #VALUE! error value if any arguments are error values or text that cannot be evaluated as a number.

Step4

Distinguish between empty cells and zero values when working with DAVERAGE by ensuring zero values will be displayed. Go to the Tools command on the menu bar, select Options and check the Zero Values check box on the View tab.

Step5

Enter the following in the first four columns of an Excel spreadsheet:

Row 1: Tree, Height, Age and Yield;
Row 2: ="=Apple" and =">10" (nothing in columns three and four);
Row 3: ="=Pear" (nothing in columns two, three and four);
Row 4: Tree, Height, Age and Yield;
Row 5: Apple, 18, 20 and 14;
Row 6: Pear, 12, 12 and 10;
Row 7: Cherry, 13, 14 and 9;
Row 8: Apple, 14, 15 and 10;
Row 9: Pear, 9, 8 and 8;
Row 10: Apple, 8, 9 and 6.

Step6

Consider this result: =DAVERAGE(A4:D10,"Yield",A1:B2) will select rows where the value in the Tree column is equal to "Apple" or "Pear" and the value in the Height column is greater than 10. The values in the Yield column will then be averaged. DAVERAGE(A4:D10,"Yield",A1:B2) will therefore be evaluated as AVERAGE(14,10)=12.