How to Calculate Geometric Mean in Excel

Calculating an average seems simple, but using the standard AVERAGE function doesn't always tell the whole story, especially when you're working with growth rates or investment returns. This is where the geometric mean comes in. This article shows you exactly how to calculate the geometric mean in Excel using its built-in function, handle percentage changes correctly, and understand why it's such a valuable metric for your analysis.

What is Geometric Mean?

While the standard average (the arithmetic mean) is calculated by adding numbers up and dividing, the geometric mean is calculated by multiplying numbers together and taking the nth root. Think of it this way: the arithmetic mean is about addition, while the geometric mean is about multiplication.

This distinction is critical when dealing with data that compounds over time, such as investment portfolios, website traffic growth, or revenue increases. The geometric mean provides a far more accurate picture of the "average" growth rate over a period because it accounts for the effects of compounding.

An Example: Why Geometric Mean Matters

Imagine you have a $100 investment. In year one, it gains 50% ($150). In year two, it loses 50% (-$75).

• Your final balance is $75.
• The arithmetic average of the returns is (50% + (-50%)) / 2 = 0%. This would suggest you broke even, which is clearly wrong.
• The geometric mean will give you the true average annual return. In this case, it’s -13.4%. This tells you that on average, your investment decreased by 13.4% each year to get you from $100 to $75.

When you need to know the constant rate of return that would yield the same result over time, the geometric mean is the correct tool for the job.

How to Calculate Geometric Mean in Excel with the GEOMEAN Function

The easiest way to find the geometric mean in Excel is by using the GEOMEAN function. It does all the heavy lifting of multiplying the values and finding the correct root for you.

The syntax for the function is:

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

Here, number1, number2, etc., can be individual numbers, cell references, or a range of cells.

Step-by-Step Guide

Let's say you want to find the geometric mean for a set of values representing population growth factors over five years.

1. Enter Your Data: Type your positive numbers into a column in Excel. For this example, let's put them in cells A2 through A6.
2. Select a Result Cell: Click on an empty cell where you want the result to appear, perhaps cell A8.
3. Type the Formula: In the formula bar, type =GEOMEAN(.
4. Select the Data Range: Drag your mouse to select the range of cells containing your data (A2:A6). Your formula will now look like this: =GEOMEAN(A2:A6).
5. Close the Parenthesis and Press Enter: Type the closing parenthesis ) and hit Enter. Excel will instantly calculate and display the geometric mean of your data set.

Important Note: The GEOMEAN function can only work with positive numbers. If your data set includes a zero or a negative number, Excel will return a #NUM! error. This is because the mathematical formula involves taking roots, which isn't defined for negative products in this context.

How to Calculate Average Growth Rate Using Geometric Mean

One of the most powerful uses for the geometric mean is finding the average rate of growth for something like monthly revenue, website traffic, or user signups. However, you can't just plug the percentage changes directly into the GEOMEAN function. You first need to convert them into growth factors.

Step 1: Convert Percentages to Growth Factors

A growth factor represents the multiplicative rate. To get it, you simply add 1 to the percentage growth rate. For example:

• A 10% increase is a growth factor of 1 + 0.10 = 1.10.
• A 5% decrease is a growth factor of 1 - 0.05 = 0.95.
• A 25% increase is a growth factor of 1 + 0.25 = 1.25.

Let’s say you have the following monthly traffic growth data in column B:

• January: 5%
• February: -2%
• March: 12%
• April: 8%

In a new column (let's say column C), create the corresponding growth factors using the formula =1+B2. Copy this formula down the column.

Step 2: Use GEOMEAN on the Growth Factors

Now that you have a list of growth factors (1.05, 0.98, 1.12, 1.08), you can use the GEOMEAN function on this new column.

In a cell, enter the formula to reference your growth factors. For example:

=GEOMEAN(C2:C5)

Excel will return a result like 1.056. This is the geometric mean of the growth factors.

Step 3: Convert the Result Back to a Percentage

The final step is to turn the average growth factor back into an average growth rate. To do this, simply subtract 1 from the result.

=GEOMEAN(C2:C5) - 1

The result will be 0.056. You can then format this cell as a percentage to see it as 5.6%. This means that, on average, your website traffic grew by 5.6% each month over the period.

Manual Calculation of Geometric Mean in Excel (For a Deeper Understanding)

If you prefer to understand the math behind the function or need to build it from scratch, you can calculate the geometric mean using a combination of the PRODUCT and COUNT functions.

The mathematical approach is to find the product of all the numbers and then take the nth root, where n is the count of the numbers. In Excel, this translates to:

=PRODUCT(range)^(1/COUNT(range))

Using this Formula:

1. Put your data into a range, for example, A2:A6.
2. PRODUCT(A2:A6) multiplies all the numbers in the range together.
3. COUNT(A2:A6) counts how many numbers are in the range (in this case, 5).
4. ^(1/5) raises the product to the power of 1/5, which is the same as taking the 5th root.

Combining it all gives you:

=PRODUCT(A2:A6)^(1/COUNT(A2:A6))

This formula will yield the exact same result as =GEOMEAN(A2:A6), but it helps you see the two distinct steps involved in the process: multiplication and finding the root.

Real-World Examples for Geometric Mean

Here are a few common scenarios where using the geometric mean is more appropriate than the arithmetic mean:

1. Investment Returns

As shown earlier, it’s the standard for calculating the average rate of return on a portfolio. It gives you the Compound Annual Growth Rate (CAGR), a foundational metric in finance. An arithmetic average would overstate the performance.

2. Marketing Campaign Performance

When analyzing the week-over-week or month-over-month growth of a Key Performance Indicator (KPI) like conversion rate or click-through rate, the geometric mean provides the correct "average" percent change over that period.

3. Scientific and Biological Data

For data that grows exponentially, like bacterial cultures or cell populations, the geometric mean is used to normalize the average growth across samples that might have vastly different scales.

Final Thoughts

In summary, calculating the geometric mean in Excel is simple with the GEOMEAN function. It's the right choice when averaging ratios or percentage growth rates because it accurately reflects compounding effects over time. Just remember to convert your percentages into growth factors (1 + rate) first to get the correct result.

Excel is fantastic for this kind of specific, in-depth analysis. But when you need to calculate and visualize growth rates across all your marketing and sales channels, the manual work of exporting data from each platform and building reports can become time-consuming. We built Graphed to solve this by connecting directly to platforms like Google Analytics, Shopify, Salesforce, and Facebook Ads. Instead of consolidating data in a spreadsheet, you can simply ask a question like, "Show me our average monthly user growth from Google Analytics" and get an answer instantly in a real-time dashboard. This lets you focus on the insights, not the manual data wrangling.