Exceljet

Quick, clean, and to the point

Excel GEOMEAN Function

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

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

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.