When it’s time to get down to analyzing your data, a good place to start is with some basic statistics, such as counting items, calculating sums and averages, finding the largest and smallest values, working out the standard deviation, and so on. These measures fall under the general rubric of descriptive statistics, and Excel offers a fistful of functions that help you get the job done. Here’s a summary:
| Function | What It Does |
COUNT(<em>value1</em>[, <em>value2</em>, ...]) |
Counts numbers |
COUNTA(<em>value1</em>[, <em>value2</em>, ...]) |
Counts non-empty cells |
COUNTBLANK(<em>value1</em>[, <em>value2</em>, ...]) |
Counts empty cells |
COUNTIF(<em>range</em>, <em>criteria</em>) |
Counts the cells in a range that match the criteria |
COUNTIFS(<em>range1</em>, <em>criteria1</em>[,<em> range2, criteria2</em>, ...]) |
Counts the cells in multiple ranges that match multiple criteria |
PERMUT(<em>number</em>, <em>number_chosen</em>) |
Counts the permutations |
COMBIN(<em>number</em>, <em>number_chosen</em>) |
Counts the combinations |
SUM(<em>number1</em>[, <em>number2</em>, ...]) |
Calculates the sum |
SUMIF(<em>range</em>, <em>criteria</em>[, <em>average_range</em>]) |
Calculates the sum of the cells that match the criteria |
SUMIFS(<em>average_range</em>, <em>range1</em>, <em>criteria1</em>[, <em>range2</em>, <em>criteria2</em>...]) |
Calculates the sum of cells in multiple ranges that match multiple criteria |
AVERAGE(<em>number1</em>[, <em>number2</em>, ...]) |
Calculates the arithmetic mean |
AVERAGEIF(<em>range</em>, <em>criteria</em>[, <em>average_range</em>]) |
Calculates the mean of the cells that match the criteria |
AVERAGEIFS(<em>average_range</em>, <em>range1</em>, <em>criteria1</em>[, <em>range2</em>, <em>criteria2</em>...]) |
Calculates the mean of cells in multiple ranges that match multiple criteria |
MEDIAN(<em>number1</em>[, <em>number2</em>, ...]) |
Calculates the median (middle) value |
MODE(<em>number1</em>[, <em>number2</em>, ...]) |
Calculates the mode (most common) value |
RANK.EQ(<em>number</em>, <em>ref</em>[, <em>order</em>]) |
Returns an item’s rank relative to the other items in a data set |
LARGE(<em>array</em>, <em>k</em>) |
Returns the kth largest item in a data set |
SMALL(<em>array</em>, <em>k</em>) |
Returns the kth smallest item in a data set |
FREQUENCY(<em>data_array</em>, <em>bins_array</em>) |
Creates a grouped frequency distribution |
VAR.S(<em>number1</em>[, <em>number2</em>, ...]) |
Calculates the variance of a sample |
VAR.P(<em>number1</em>[, <em>number2</em>, ...]) |
Calculates the variance of a population |
STDEV.S(<em>number1</em>[, <em>number2</em>, ...]) |
Calculates the standard deviation of a sample |
STDEV.P(<em>number1</em>[, <em>number2</em>, ...]) |
Calculates the standard deviation of a population |
CORREL(<em>array1</em>, <em>array2</em>) |
Calculates the correlation between two data sets |
