Excel 2007 Database Functions - dummies

By Stephen L. Nelson

Part of Excel 2007 Data Analysis For Dummies Cheat Sheet

Excel 2007’s data analysis tools include a set of database functions for making statistical calculations using information from lists. The following table shows these functions and explains what each does:

Function Description
DAVERAGE Calculates arithmetic mean
DCOUNT Counts the number of cells with values
DCOUNTA Counts the number of cells that aren’t empty
DGET Returns a value from a database list
DMAX Finds the largest value in a list
DMIN Finds the smallest value in a list
DPRODUCT Calculates the product of values matching criteria
DSTDEV Calculates the standard deviation of a sample
DSTDEVP Calculates the standard deviation of a population
DSUM Calculates the sum of values matching criteria
DVAR Calculates the variance of a sample
DVARP Calculates the variance of a population

All these database functions use a standard three-argument syntax. For example, the DAVERAGE function looks like this: =DAVERAGE(database,field,criteria)where database is a range reference to the Excel list that holds the value you want to examine, field tells Excel which column in the database to examine, and criteria is a range reference that identifies the fields and values used to define your selection criteria. The field argument can be a cell reference holding the field name, the field name enclosed in quotation marks, or a number that identifies the column (1 for the first column, 2 for the second column, and so on).