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],...)
  • number1 - First value or reference.
  • number2 - [optional] Second value or reference.

How to use 

The Excel GEOMEAN function calculates the geometric mean. Geometric mean is the average of a set of products — technically, the nth root of n numbers. The general formula for the geometric mean of n numbers is the nth root of their product. The equation looks like this:

 

For example, given two numbers, 4 and 9, the long-hand calculation for the geometric mean is 6:

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

The GEOMEAN function returns the same result:

=GEOMEAN(4,9) // returns 6

By contrast, the arithmetic mean is 6.5:

=(4+9)/2=6.5

The GEOMEAN function takes multiple arguments in the form number1number2number3, etc. up to 255 total. Arguments can be a hardcoded constant, a cell reference, or a range. Often, a single range or array is used instead of multiple arguments, as seen in the example worksheet.

Examples

In the example shown, GEOMEAN is used to calculate a compound annual growth rate. To do 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.
  • If any provided values are less than or equal to zero, GEOMEAN returns #NUM!
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.