Database functions: DAVERAGE

In a prior example we saw how data filtering could be used to reduce the size of the data set before doing analysis.

The database functions perform basic data analysis - but they also have advanced data filtering built in.

The DAVERAGE function returns the average of the values in a database column satisfying a specified condition.

With the database functions - such as DAVERAGE, the criteria are defined in the spreadsheet. This is illustrated below in cells D13:E14. The criteria matrix normally has the same colums and headers as the main database table. Each row of the criteria matrix corresponds to a conditon to be applied to the database when filtering. Criteria in the same row are ANDed, while criteria in different rows are ORed

In the example below, we have configured the criteria to average rows whose age is <25 AND whose salaries are less than $50,000. These criteria are both selected in the formula of B20. For the other example formulas, we select either the age or the salary condition, but not both.

In B18, we are averaging the filtered ages, but the other example formulas are averaging the salary.

This entry was posted on Monday, Novembe

Дата добавления: 2015-08-03