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.
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.
GEOMEAN formula examples
To calculate Compound Annual Growth Rate (CAGR) in Excel, the average rate of return for an investment over a period of time, you can use several approaches. In the example shown, the formula in H7 is:
=(C11/C6)^(1/...
Related functions
