How to Use Statistical Functions to Calculate Means, Modes, and Medians in Excel

By Stephen L. Nelson, E. C. Nelson

Excel provides you with a handful of statistical functions for calculating means, modes, and medians. Take a look at the following descriptions for examples of how to use these statistical functions.

AVEDEV: An average absolute deviation

The AVEDEV function provides a measure of dispersion for a set of values. To do this, the function looks at a set of values and calculates the average absolute deviation from the mean of the values. The function uses the syntax

=AVEDEV(number1,[number2])

where number1, [number2] is a worksheet reference to the range that stores the values.

As is the case with many other simple statistical functions, you can include several arguments as part of the range argument in the AVEDEV function. For example, the formulas =AVEDEV(B1,B2:B5,B6:B7,B8,B9) and =AVEDEV(B1:B9) are equivalent.

Suppose you have three values — 100, 200, and 300 — in the worksheet range that you supply to the AVEDEV function. The average of these three values is 200, calculated as (100+200+300)/3. The average of the deviations from the mean is 66.6667, calculated as:

(|100-200|+|200-200|+|300-200|)/3

Note: The AVEDEV function calculates the average of the absolute value of the deviation. For this reason, the function calculates absolute differences, or deviations, from the mean.

The AVEDEV function isn’t used in practice. Mostly a teaching tool, educators and trainers sometimes use the average deviation measure of dispersion to introduce the more useful but also more complicated measures of dispersion: the standard deviation and variance.

AVERAGE: Average

The AVERAGE function calculates the arithmetic mean for a set of values. The function uses the syntax

=AVERAGE(number1,[number2])

where number1, [number2] is a worksheet reference to the range that stores the values.

If your argument includes the three values — , 100,200 , and 300 — the function returns the value 200 because (100+200+300)/3 equals 200.

AVERAGEA: An alternate average

The AVERAGEA function, like the AVERAGE function, calculates the arithmetic mean for a set of values. The difference with the AVERAGEA function, however, is that AVERAGEA includes cells with text and the logical value for FALSE in its calculations as 0. The AVERAGEA function includes the logical value for TRUE in its calculations as 1. The function uses the syntax

=AVERAGEA(number1,[number2])

where number1, [number2] is a worksheet reference to the range that stores the values — and possibly text as well as logical values.

If your argument includes three values — 100, 200 , and 300 — and three text labels in the worksheet range that you supply to the AVERAGEA function, the function returns the value 100 because (100+200+300+0+0+0)/6 equals 100.

As is the case with the AVERAGE function, you can supply up to 255 arguments to the AVERAGEA function.

TRIMMEAN: Trimming to a mean

The TRIMMEAN function calculates the arithmetic average of a set of values but only after discarding a specified percentage of the lowest and highest values from the set. The function uses the syntax

=TRIMMEAN(array,percent)

where array is the range holding the values and percent is the decimal value that gives the percentage of values that you want to discard. For example, to calculate the arithmetic mean of the values stored in the worksheet range C2:C10 only after discarding 10 percent of the data — the top 5 percent and the bottom 5 percent — you use the following formula:

image0.jpg

=TRIMMEAN(C2:C10,0.1)

MEDIAN: Median value

The MEDIAN function finds the middle value in a set of values: Half the values fall below and half the values fall above the median. The function uses the syntax

=MEDIAN(number1,[number2])

Note: You can supply up to 255 arguments to the MEDIAN function.

If you use the MEDIAN function to find the median of a range holding the values 1, 2, 3, and 4, the function returns the value 2.5 . Why? Because if you have an even number of data entries, Excel calculates a median by averaging the two middle values.

MODE: Mode value

The MODE function finds the most common value in your data set, but the function ignores empty cells and cells that store text or return logical values. The function uses the syntax

=MODE(number1,[number2])

Note: You can supply up to 255 arguments to the MODE function.

GEOMEAN: Geometric mean

The GEOMEAN function calculates the geometric mean of a set of values. The geometric mean equals the nth root of the product of the numbers. The function uses the syntax

=GEOMEAN(number1,[number2]...)

where number1 and, optionally, other similar arguments supply the values that you want to geometrically average.

HARMEAN: Harmonic mean

The HARMEAN function calculates the reciprocal of the arithmetic mean of the reciprocals of a data set. The function uses the syntax

=HARMEAN(number1,[number2]...)

where number1 and, optionally, other similar arguments supply the values that you want to harmonically average.