How to Calculate the Geometric Mean in Excel With GEOMEAN

The geometric mean in Excel is one function: =GEOMEAN(range). It multiplies the values together and takes the nth root, which makes it the right average for things that grow by multiplying, such as investment returns, sales growth or price changes. This guide shows the formula, how to use it with percentages, why it differs from the normal average, and how to fix the #NUM! error.

What is the geometric mean?#

The geometric mean is the nth root of the product of n positive numbers. In plain words: multiply all the values, then find the number that, multiplied by itself n times, gives the same product.

For 2 and 8, the product is 16. The square root of 16 is 4, so the geometric mean is 4. The ordinary average of 2 and 8 is 5.

The geometric mean is never larger than the arithmetic mean. The two are equal only when every value is the same.

How to use GEOMEAN in Excel#

GEOMEAN takes numbers, cells or ranges, just like AVERAGE:

=GEOMEAN(number1, [number2], ...)

To find the geometric mean of the values in A2:A6:

1. Click an empty cell.
2. Type =GEOMEAN(A2:A6).
3. Press Enter.

GEOMEAN works in every current version of Excel on Windows and Mac, including Microsoft 365 and Excel for the web. Like AVERAGE, it skips empty cells and text inside a range. The AVERAGE function guide covers how those skipped cells work.

How to find the average growth rate with GEOMEAN#

For returns or growth rates, you can't put the percentages straight into GEOMEAN. A return of -5% is a negative number, and a return of 10% is 0.1, which would give a meaningless result. Instead, turn each rate into a growth factor: 1 plus the rate.

In C2, type =1+B2 and fill down. Then find the average yearly growth:

=GEOMEAN(C2:C5)-1

The result is 0.0686, or 6.86% when you format the cell as a percentage. The "minus 1" turns the average growth factor back into a rate.

You can skip the helper column. In Microsoft 365 and Excel 2021, this works as a normal formula:

=GEOMEAN(1+B2:B5)-1

In Excel 2019 and older, it is an array formula. Confirm it with Ctrl+Shift+Enter on Windows (on a Mac, Ctrl+Shift+Return or ⌘+Shift+Return, depending on your version) or it may return the wrong value. If you need to average percentages that aren't growth rates, such as pass rates, see how to average percentages correctly.

Geometric mean vs average: which is right?#

Use the geometric mean when values multiply over time, and the ordinary AVERAGE when they add up. Here is why it matters with the table above.

The simple average of the four returns is (10% - 5% + 20% + 4%) / 4 = 7.25%. That sounds fine, but test it. Start with 1,000:

Only the geometric mean lands on the real end value of 1,304.16. The simple average overstates the growth because a loss followed by a gain does not cancel out. Losing 50% and then gaining 50% leaves you at 75%, not 100%.

This is the same idea as the compound annual growth rate. The CAGR formula guide shows the shortcut =(End/Start)^(1/Years)-1, which gives the same 6.86% here: =(1304.16/1000)^(1/4)-1. Use CAGR when you only know the first and last values, and GEOMEAN when you have each period's rate.

For skewed data that adds up, such as salaries or house prices, the median is often a better middle value than either mean. The comparison of average vs median explains when to choose it.

How to calculate a geometric mean without GEOMEAN#

You can build the geometric mean from PRODUCT, POWER and COUNT. This is useful in tools that lack GEOMEAN, or when you want to show each step:

=POWER(PRODUCT(C2:C5),1/COUNT(C2:C5))

• PRODUCT(C2:C5) multiplies the factors: 1.10 × 0.95 × 1.20 × 1.04 = 1.30416.
• COUNT(C2:C5) counts them: 4.
• POWER(..., 1/4) takes the 4th root: 1.0686.

Subtract 1 to get the rate, 6.86%. You can also write =PRODUCT(C2:C5)^(1/COUNT(C2:C5)), since ^ is the power operator. With a very long list, PRODUCT can grow too large or too small for Excel. In that case, GEOMEAN is safer.

Why does GEOMEAN return #NUM!?#

GEOMEAN returns #NUM! when any value is zero or negative. The geometric mean is only defined for positive numbers. Check these causes:

1. You used rates instead of factors. A -5% return is -0.05. Use 1+rate, so it becomes 0.95.
2. A real zero in the data. A zero makes the product zero. Decide whether that row belongs in the calculation, or exclude it.
3. A loss of 100% or more. A factor of 0 or below means the investment was wiped out, and no average growth rate exists.
4. Numbers stored as text. These are skipped inside a range, so the count and result can be off without any error. Convert them with VALUE first.

A #VALUE! error usually means you typed text directly into the formula, such as =GEOMEAN("ten",5).

Related averages: weighted and harmonic#

Excel has two other "special" averages you may meet:

• Weighted average: some values count more than others, such as course grades by credit hours. Use =SUMPRODUCT(values,weights)/SUM(weights). The weighted average guide walks through it.
• Harmonic mean: =HARMEAN(range), used for averaging rates such as speed over equal distances.

For everyday summaries, AVERAGE, MIN and MAX still do most of the work. See how to use AVERAGE, MIN and MAX for a refresher.

Practise it#

The geometric mean builds on two skills: averaging a range and working with percent change. Practise AVERAGE, MIN and MAX in a live spreadsheet, then practise percent change and growth: you type each formula and it checks it instantly. Or try the growth-rate exercise on this page first.