How to calculate Geometric Mean?

In this Excel tutorial you will teach yourself how to calculate geometric mean in Excel.

Geomean function

To calculate a geometric mean in Excel you need to know how to use GEOMEAN function.

Geomean syntax

=GEOMEAN (number1, [number2], …)

  • number1 is a first value to calculate. It can be a number, reference or a range.
  • number2 is optional argument to provide more values for calculations. Up to 255 arguments can be provided.

Arguments cannot be less than 0. When any number is less than 0, GEOMEAN function will return #NUM! error.

When text value will be provided as a argument the function will return #VALUE! error.

How to calculate geometric mean in Excel?

Let’s see example data of portfolio investment.

geometric mean data table

We know percent return every year. So it is easy to calculate return on investment.

To calculate it you need to divide final value by starting value. -1 is just to get percentage of the change. Excel formula is =B3/B2-1

geometric mean return on investment

Let’s first calculate arithmetic average using Excel average function.

Excel formula this time is =AVERAGE(C13:C17)

geometric mean aritmetic average

Calculating Geometric Mean by Values

First method to calculate geometric mean is to calculate values. Here’s geometric formula for this method:

Geometric Average = (Result Value / Starting Value ^ (1 / Investment Period) – 1

For this method just use Excel formula =(B17/B2)^(1/$A$17)-1

geometric mean calculation

Calculating Geometric Mean by percent return

The second method to calculate geometric average is to base on return percentage.

See also  Exponentially Weighted Moving Average (EWMA) in Excel

Use Sumproduct function for that purpose.

=SUMPRODUCT(GEOMEAN(C3:C17+1)-1)

geometric mean calculation based on returns

To check if geometric mean calculations are proper you can calculate it step by step.

To calculate that you need multiply starting value by 1 + geometric mean and power it by investment periods number.

Formula is =B2*(1+C21)^15

geometric mean check

Results are correct. Geometric average calculator worked fine. You can download it here.