How to use the Excel GEOMEAN function

Summary

The Excel GEOMEAN function returns the geometric mean for a set of numeric values. Geometric mean can be used to calculate average rate of return with variable rates.

Purpose

Calculate geometric mean

Return value

Calculated mean

Syntax

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

Arguments

number1 - First value or reference.
number2 - [optional] Second value or reference.

Version

Excel 2003

Usage notes

The Excel GEOMEAN function calculates the geometric mean. Geometric mean is the average rate of return of a set of values calculated using the products of the terms. The general formula for the geometric mean of n numbers is the nth root of their product. For example:

=GEOMEAN(4,9) // returns 6

The long-hand calculation would be:

=(4*9)^(1/2)
=(36)^(1/2)
=6

The arithmetic mean would be (4 + 9)/2 = 6.5.

In the example shown, GEOMEAN is used to calculate a compound annual growth rate. To to this we use the growth factor values in column D in the GEOMEAN function, then subtract 1. The formula in G7 is:

=GEOMEAN(D6:D10)-1

Notes

Arguments can be numbers, names, arrays, or references that contain numbers.
Empty cells, and cells that contain text or logical values are ignored.

Related functions

Excel AVERAGE Function

The Excel AVERAGE function calculates the average (arithmetic mean) of supplied numbers. AVERAGE can handle up to 255 individual arguments, which can include numbers, cell references, ranges, arrays, and constants.

Excel TRIMMEAN Function

The Excel TRIMMEAN function calculates mean (average) while excluding outliers. The number of data points to exclude is provided as a percentage.

Excel HARMEAN Function

The Excel HARMEAN function returns the harmonic mean for a set of numeric values. The harmonic mean is the reciprocal of the arithmetic mean of reciprocals. Harmonic mean can be used to calculate a mean that reduces the impact of outliers.

Excel RRI Function

The Excel RRI function returns an equivalent interest rate for the growth of an investment. You can use RRI to calculate Compound Annual Growth Rate (CAGR) in Excel.