Excel Database Functions for Statistical Calculations

By Stephen L. Nelson, E. C. Nelson

Excel provides a valuable set of handy-to-use database functions for making statistical calculations using information from lists. These functions are incredibly helpful for analyzing your data. The following table includes many of the most useful database functions and a description of what they do. After the table is an explanation of the standard three-argument syntax used by all of these functions.

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:


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).