Excel Database Functions for Statistical Calculations
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.
|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).