Excel Functions You Need to Know: Measures of Center

8 min readApr 4, 2023

Part 2a of a comprehensive series on Excel functions.

Welcome to Part II of the Excel Functions Demystified series, where we’ll begin coverage of Statistical Function in Excel. There are quite a few statistical functions available in Excel, so this will be split into 8 subparts:

Measures of Center
Counting and Conditional Counting
Probability Distributions pt.1
Probability Distributions pt.2
Probability Distributions pt.3
Regression and Correlation
Statistical Tests
Miscellaneous Statistical Functions pt.1
Miscellaneous Statistical Functions pt.2

This article covers subpart 1: Measures of Center. A measure of center is a statistical value that represents the central or typical value of a set of data. A measure of center is a summary statistic that provides information about the distribution of the data and where the data is concentrated.

Excel has a variety of built-in functions to describe measures of center:

1. AVEDEV Function:

The AVEDEV function calculates the average of the absolute deviations of data points from their arithmetic mean. In other words, it measures the average distance between each data point and the mean. The syntax for the AVEDEV function is:

=AVEDEV(number1, [number2], …)

Where number1, number2, etc. are the values or cell references that you want to calculate the average deviation of.

2. AVERAGE Function:

The AVERAGE function calculates the arithmetic mean of a set of numbers. It is one of the most commonly used functions in Excel, and it’s very easy to use. The syntax for the AVERAGE function is:

=AVERAGE(number1, [number2], …)

Where number1, number2, etc. are the values or cell references that you want to calculate the average of.

3. AVERAGEA Function:

The AVERAGEA function is similar to the AVERAGE function, but it includes non-numeric values in the calculation (Evaluated as a 0). This can be useful when you have a mix of numbers and text in your data set. The syntax for the AVERAGEA function is:

=AVERAGEA(value1, [value2], …)

Where value1, value2, etc. are the values or cell references that you want to calculate the average of.

4. AVERAGEIF Function:

The AVERAGEIF function calculates the average of a range of cells that meet a specific criteria. The syntax for the AVERAGEIF function is:

=AVERAGEIF(range, criteria, [average_range])

Where range is the range of cells that you want to evaluate, criteria is the condition that you want to test for, and average_range is the range of cells that you want to average (if different from the range).

5. AVERAGEIFS Function:

The AVERAGEIFS function is similar to the AVERAGEIF function, but it allows you to apply multiple criteria to the calculation. The syntax for the AVERAGEIFS function is:

=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Where average_range is the range of cells that you want to average, criteria_range1 is the range of cells that you want to evaluate for the first criteria, criteria1 is the condition that you want to test for in criteria_range1, and so on.

6. GEOMEAN Function:

The geometric mean and the arithmetic mean, which is also called simply the mean, are both measures of central tendency, but they differ in how they weigh the values in a data set.

The arithmetic mean is the sum of all values in a data set divided by the number of values. It gives equal weight to each value in the data set.

For example, if we have a data set of numbers {2, 3, 5, 7}, the arithmetic mean would be (2+3+5+7)/4 = 4.25.

The geometric mean, on the other hand, is calculated by taking the nth root of the product of all values in a data set, where n is the number of values. It gives more weight to the smaller values in the data set.

For example, if we have the same data set {2, 3, 5, 7}, the geometric mean would be (2 x 3 x 5 x 7)^(1/4) = 3.68

The geometric mean is particularly useful when dealing with data that is exponential or grows at a constant rate, such as population growth or compound interest. In these cases, the geometric mean can provide a more accurate representation of the typical value than the arithmetic mean, which can be heavily influenced by the larger values in the data set.

The GEOMEAN function calculates the geometric mean of a set of numbers. It is useful for calculating growth rates, such as compound interest rates. The syntax for the GEOMEAN function is:

=GEOMEAN(number1, [number2], …)

Where number1, number2, etc. are the values or cell references that you want to calculate the geometric mean of.

7. HARMEAN Function:

The harmonic mean is another type of mean that is less commonly used than the arithmetic and geometric means. It is a type of weighted average that is particularly useful when dealing with rates or ratios.

The harmonic mean is calculated by taking the reciprocal of each value in a data set, calculating the arithmetic mean of those reciprocals, and then taking the reciprocal of that result. In other words, the harmonic mean of n values is:

Harmonic mean = n / (1/x1 + 1/x2 + … + 1/xn)
where x1, x2, …, xn are the individual values in the data set.

The harmonic mean gives more weight to the smaller values in the data set, just like the geometric mean. However, unlike the geometric mean, the harmonic mean is not affected by extreme values or outliers. This makes it a useful measure of central tendency when dealing with skewed data or data with extreme values.

The harmonic mean is particularly useful when dealing with rates, such as average speed or fuel efficiency. For example, if a car travels 60 miles at 30 miles per hour and then travels back the same distance at 60 miles per hour, the average speed for the entire trip is not the arithmetic mean of 30 and 60 (which would be 45 mph), but rather the harmonic mean of 30 and 60, which is 40 mph.

The HARMEAN function calculates the harmonic mean of a set of numbers. It is useful for calculating rates, such as average speed. The syntax for the HARMEAN function is:

=HARMEAN(number1, [number2], …)

Where number1, number2, etc. are the values or cell references that you want to calculate the harmonic mean of.

8. MEDIAN Function:

The MEDIAN function calculates the median value in a set of numbers. The median is the middle value in a set of numbers when they are arranged in order. If there is an even number of values, the median is the average of the two middle values. The syntax for the MEDIAN function is:

=MEDIAN(number1, [number2], …)

Where number1, number2, etc. are the values or cell references that you want to calculate the median of.

9. TRIMMEAN Function:

The TRIMMEAN function calculates the mean of a set of numbers after excluding a certain percentage of the highest and lowest values. This can be useful when you have outliers in your data that are skewing the average. The syntax for the TRIMMEAN function is:

=TRIMMEAN(array, percent)

Where array is the range of cells that you want to evaluate and percent is the percentage of values to exclude from the calculation (e.g., 10% to exclude the highest and lowest 10% of values).

10. MAX Function:

The MAX function returns the highest value in a set of numbers. The syntax for the MAX function is:

=MAX(number1, [number2], …)

Where number1, number2, etc. are the values or cell references that you want to find the highest value of.

11. MAXA Function:

The MAXA function is similar to the MAX function, but it includes non-numeric values in the calculation. The syntax for the MAXA function is:

=MAXA(value1, [value2], …)

Where value1, value2, etc. are the values or cell references that you want to find the highest value of.

12. MAXIFS Function:

The MAXIFS function returns the maximum value in a range of cells that meet one or more criteria. The syntax for the MAXIFS function is:

=MAXIFS(range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Where range is the range of cells that you want to evaluate, criteria_range1 is the range of cells that you want to evaluate for the first criteria, criteria1 is the condition that you want to test for in criteria_range1, and so on.