The Excel DAVERAGE Function

The Excel Database Functions are designed to assist you when working with an Excel database.

A database typically takes the form of a large table of data, where each row in the table stores an individual record. Each column in the spreadsheet table stores a different field (or type of information) for each record.

The database functions perform basic operations, such as count, max, min, etc, but they enable the user to specify criteria, so that the operation is performed on selected records only. Other records in the database are ignored.

Basic Description

The Excel Daverage function calculates the average (statistical mean) of values in a field (column) in a database for selected records, that satisfy user-specified criteria.

Note that the Excel database functions are not case sensitive. So, for example, the criteria ="North" will be satisfied by cells containing the text "North" or "north".

Excel Daverage Function Examples

A

B

C

D

1

Quarter

Area

Sales Rep.

Sales

2

1

North

Jeff

$223,000

3

1

North

Chris

$125,000

4

1

South

Carol

$456,000

5

1

South

Tina

$289,000

6

2

North

Jeff

$322,000

7

2

North

Chris

$340,000

8

2

South

Carol

$198,000

9

2

South

Tina

$222,000

10

3

North

Jeff

$310,000

11

3

North

Chris

$250,000

12

3

South

Carol

$460,000

13

3

South

Tina

$395,000

14

4

North

Jeff

$261,000

15

4

North

Chris

$389,000

16

4

South

Carol

$305,000

17

4

South

Tina

$188,000

The following examples are based on the above simple database on the right, which stores the sales figures for four sales representatives, over the four quarters of a year.

Example 1

In the example below, the Daverage function is used to calculate the average quarterly sales in quarters 2, 3 and 4, for the Sales Rep. "Tina". The criteria are specified in cells F1 - G2 and the format of the Daverage function is shown in cell F3.

F

G

1

Quarter

Sales Rep.

2

>1

Tina

3

=DAVERAGE( A1:D17, "Sales", F1:G2 )

The above Daverage function calculates the average of the values in cells D9, D13 & D17, and therefore returns the value $268,333.

Example 2

In the example below, the Daverage function is used to calculate the average sales in quarter 1, by sales reps in the "South" area.

F

G

1

Quarter

Area

2

1

South

3

=DAVERAGE( A1:D17, "Sales", F1:G2 )

The above Daverage function calculates the average of the values in cells D4 and D5 and so returns the value $372,500.

Note that, in the above two examples, instead of typing in "Sales" for the field argument, we could have simply used the number 4 (to denote the 4th column of the database).