How Do You Calculate the Geometric Mean in Excel?

If you have ever averaged a set of percentage growth rates in Excel and got a number that felt slightly too flattering, you have already met the problem the geometric mean exists to solve.

Excel has a dedicated function for it, and almost nobody uses it. The function is one word long, takes one argument, and is the correct tool for a specific and very common class of numbers: anything that compounds. Growth rates, investment returns, ratios, index values, rates of change measured period over period.

This guide covers the formula, the one transformation that makes it work on percentages, the two input values that will break it, and the honest answer to when you should reach for `AVERAGE` instead.

What is the formula for geometric mean in Excel?

`=GEOMEAN(A2:A8)`

That is the whole answer. Point it at a range of positive numbers and it returns the geometric mean.

Microsoft's own documentation describes the function this way: "Returns the geometric mean of an array or range of positive data. For example, you can use GEOMEAN to calculate average growth rate given compound interest with variable rates."

The syntax is `GEOMEAN(number1, [number2], ...)`, and the argument definition is worth reading closely: "Number1 is required, subsequent numbers are optional. 1 to 255 arguments for which you want to calculate the mean. You can also use a single array or a reference to an array instead of arguments separated by commas."

So both of these are valid, and they do the same thing:

• `=GEOMEAN(A2:A8)` — one range reference, which is what you will use 95% of the time
• `=GEOMEAN(4,5,8,7,11,4,3)` — up to 255 values typed directly

Microsoft's worked example uses exactly that second set of numbers. With 4, 5, 8, 7, 11, 4 and 3 in cells A2 to A8, `=GEOMEAN(A2:A8)` returns 5.476987. The arithmetic mean of the same seven numbers is 6. That gap is the entire point of the function.

Getting the numbers right is step one. Getting found is step two. Claim your free SEO audit.

What the geometric mean actually is, and why Excel gives it its own function

The arithmetic mean adds your values and divides by how many there are. The geometric mean multiplies your values together and takes the nth root, where n is how many values you had.

That difference sounds academic until you notice what each one assumes about your data.

The arithmetic mean assumes your values add up

Averaging seven test scores with `AVERAGE` is correct, because test scores add. A student's total is the sum of their papers, so the average paper is the total divided by the count.

Averaging seven years of investment returns with `AVERAGE` is wrong, because returns do not add — they multiply. A 50% gain followed by a 50% loss does not leave you flat. It leaves you down 25%, because you lost half of a bigger number than you gained half of.

The arithmetic mean of +50% and -50% is zero. The geometric mean of the same two years is roughly -13.4%, which compounds over two years to exactly the -25% you actually experienced. Only one of those two numbers describes reality.

Multiplicative data is more common than people think

The list of things that compound rather than add is longer than most spreadsheets acknowledge:

• Revenue or user growth measured period over period
• Investment and portfolio returns
• Inflation and price index changes
• Population growth
• Any ratio — price-to-earnings, conversion rate relative to baseline, click-through-rate indexes
• Interest rates, which is the case Microsoft's documentation names explicitly

If your values are percentages of a previous value rather than quantities in their own right, the geometric mean is the right average.

How Excel handles text, blanks and zeros inside the range

This is where GEOMEAN surprises people, and Microsoft's remarks are specific about it.

On what counts: "Logical values and text representations of numbers that you type directly into the list of arguments are counted."

On what gets skipped: "If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included."

Read that second sentence twice. A blank cell inside your range is quietly dropped. A cell containing zero is not — it is included, and as you will see below, a single zero destroys the result.

And on hard failures: "Arguments that are error values or text that cannot be translated into numbers cause errors."

How do you calculate the geometric mean in Excel, step by step?

For a column of plain positive numbers:

1. Put your values in a single column — say A2 through A8 — with nothing but numbers in the cells you intend to include.
2. Click an empty cell where you want the result.
3. Type `=GEOMEAN(` and then drag to select your range, or type the range reference directly.
4. Close the bracket and press Enter.
5. Format the result cell to the number of decimal places you actually need, because GEOMEAN will happily hand you six.

That is it for raw values. Growth rates need one extra step, and skipping it is the single most common mistake with this function.

Want to know which of your pages are actually compounding? Claim your free SEO audit.

How do you use GEOMEAN on growth rates and percentages?

You cannot feed growth rates straight into GEOMEAN. A year of -8% growth is a negative number, and negative numbers break the function entirely.

The fix is to convert each rate into a growth factor first — the multiplier that takes you from the start of the period to the end. A 12% gain is a factor of 1.12. An 8% loss is a factor of 0.92. Flat is 1.00.

The whole calculation in three moves:

1. In a helper column, convert each rate to a factor: `=1+B2` if B2 holds 0.12, or `=1+B2/100` if B2 holds 12.
2. Take the geometric mean of the factors: `=GEOMEAN(C2:C8)`.
3. Convert the answer back to a rate by subtracting one: `=GEOMEAN(C2:C8)-1`.

You can collapse all three into a single array-aware formula if you prefer not to keep a helper column:

• `=GEOMEAN(1+B2:B8)-1` in a current version of Excel, where the range already holds decimals like 0.12 and -0.08

The result is your compound annual growth rate — the single constant rate that, applied every period, would have produced the same end value as your messy real sequence. That is a genuinely useful number, and it is almost always lower than the arithmetic average of the same rates.

Why the helper column is usually worth keeping

Array formulas are elegant and they hide your workings. When a stakeholder asks why the average growth rate in your deck is 6.1% and not the 7.4% they calculated in their head, a visible factor column is the fastest way to show them. Collapse it only when the sheet is finished and the logic is settled.

When should you use the geometric mean instead of the average?

A short decision rule that holds up in practice:

• Use GEOMEAN when each value is a rate of change, a ratio, an index, or anything measured relative to the value before it.
• Use AVERAGE when each value is an independent quantity measured in real units — dollars, visitors, minutes, scores.
• Use neither when your data is badly skewed by outliers and you only want a typical value, in which case the median is the honest choice.

A practical test: ask whether it makes sense to multiply your values together. If multiplying seven months of revenue figures gives you a meaningless number, you want AVERAGE. If multiplying seven growth factors gives you the total growth over the whole period — which it does — you want GEOMEAN.

Averages are easy. Rankings are not. Claim your free SEO audit.

Where GEOMEAN falls down

Four limitations, in rough order of how often they bite.

Zero and negative values return #NUM!

Microsoft states it plainly: "If any data point less than or equal to 0, GEOMEAN returns the #NUM! error value."

This is not a bug or an arbitrary restriction. The geometric mean multiplies everything together, so one zero makes the product zero and the nth root of zero is zero — a meaningless average. Negative numbers put you into fractional roots of negatives, which have no real answer. The function refuses rather than guessing.

In practice this means raw percentage changes will error out the moment one period is negative, which is why the growth-factor conversion above is not optional.

Blank cells are silently dropped

Because empty cells in a referenced range are ignored, a range of twelve cells with two blanks returns the geometric mean of ten values. Excel will not warn you. If those blanks represent genuine zero-growth periods, your answer is now wrong and nothing on screen says so. Fill them with 1 (as a factor) or 0 (as a rate, then converted) rather than leaving them empty.

It is harder to explain than the average

Nobody has to be talked through what an average is. A geometric mean in a board deck will draw questions, and the honest answer — "the arithmetic average overstates compound growth" — needs a sentence of setup. Budget for that, and label the cell clearly.

It is the wrong answer for additive data

Using GEOMEAN on monthly revenue in dollars is not a more sophisticated average. It is simply the wrong function, and it will return a number that is lower than the true average for no defensible reason.

GEOMEAN vs AVERAGE vs RRI vs doing it by hand

Four ways to get at the same family of questions, and when each is right.

AVERAGE is for additive quantities. Fast, universally understood, and wrong for compound growth.

GEOMEAN is for multiplicative data where you have every individual period's value or factor. It is the right default for averaging a series of rates.

RRI is for when you only have the endpoints. Microsoft describes it as a function that "Returns an equivalent interest rate for the growth of an investment," with the syntax `RRI(nper, pv, fv)` — the number of periods, the present value, and the future value. If you know revenue was 1.2m five years ago and 2.1m now, `=RRI(5,1200000,2100000)` gives you the compound rate without needing the years in between. Note its error behaviour: "If argument values are not valid, RRI returns the #NUM! error value," and "If arguments are not using valid data types, RRI returns the #VALUE! error value."

By hand — `=PRODUCT(A2:A8)^(1/COUNT(A2:A8))` — does the same arithmetic as GEOMEAN and is occasionally useful for showing the working in a teaching context. It is also more fragile: PRODUCT and COUNT treat blanks and zeros differently from GEOMEAN, so the two formulas can disagree on a messy range. Prefer the built-in function.

GEOMEAN and RRI should agree when applied to the same underlying series, which makes them a useful cross-check on each other. If they disagree by more than rounding, something in your factor column is wrong.

Stop guessing which pages are worth the effort. Claim your free SEO audit.

Final Thoughts

The geometric mean is not an advanced technique. It is one function, one argument, and one small transformation when your inputs are percentages — and it is the difference between a growth figure that is defensible and one that quietly flatters the business.

The rule to remember: if your numbers describe change relative to what came before, average them with GEOMEAN. If they describe quantities in their own right, average them with AVERAGE. The errors come almost entirely from applying the second rule to the first kind of data.

Two habits make this safe in a real workbook. Keep a visible growth-factor column rather than a clever one-cell array formula, so the logic survives being handed to someone else. And check your range for blanks before you trust the output, because that is the one failure mode Excel will not flag for you.

If you are picking your way through Excel's family of averages more generally, our guide to finding the median in Excel covers the third member of the set and when a middle value beats any kind of mean.