How to calculate Geometric Mean in Excel
In this Excel tutorial, you will learn how to calculate the 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.
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.
Let’s first calculate arithmetic average using Excel average function.
Excel formula this time is =AVERAGE(C13:C17).
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.
Calculating Geometric Mean by percent return
The second method to calculate geometric average is to base on return percentage.
Use Sumproduct function for that purpose.
=SUMPRODUCT(GEOMEAN(C3:C17+1)-1)
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.
Results are correct. Geometric average calculator worked fine. You can download it here.
Leave a Reply