Need to calculate the geometric mean in Google Sheets? The GEOMEAN function is designed just for that—whether you’re analyzing growth rates, investment returns, or ratios, it helps you find the “multiplicative average” of your dataset.
In this tutorial, you’ll learn:
- What the GEOMEAN function is and when to use it
- How to calculate geometric mean with and without the function
- How to avoid common GEOMEAN errors
- Real-world formula examples for everyday use
What Is the GEOMEAN Function in Google Sheets?
The GEOMEAN function in Google Sheets returns the geometric mean of a set of positive numbers. It’s especially useful when you’re dealing with values that multiply over time—such as compound growth rates.
GEOMEAN Syntax:
GEOMEAN(value1, [value2, ...])value1: The first number or range.[value2, ...]: Optional additional numbers or ranges.
How to Calculate Geometric Mean Without GEOMEAN
Before jumping into the function, here’s how geometric mean works manually. This helps you understand what GEOMEAN does behind the scenes.
Example 1 – Manual Calculation with 3 Numbers
Find the geometric mean of 5, 10, and 25.
- Multiply all values:
5 * 10 * 25 = 1250 - Take the cube root (3rd root) of 1250:
=1250^(1/3)
Result:10.77
For n values, the formula becomes:
=product^(1/n)
where n is the number of values.
👉 Want to learn more about nth roots? See: Square Root, Cube Root, and Nth Root in Google Sheets
Example 2 – Manual Calculation with 5 Numbers
Find the geometric mean of 5, 10, 15, 20, 25.
- Multiply values:
=5*10*15*20*25 → 375,000 - Find the 5th root:
=375000^(1/5)
Result:13.03
How to Use the GEOMEAN Function in Google Sheets
Let’s now see how the GEOMEAN function simplifies this process.
Formula Example 1 – With Individual Numbers
=GEOMEAN(5, 10, 15, 20, 25)Result: 13.03
Formula Example 2 – With a Range
Assume B3:B7 contains the values 5, 10, 15, 20, 25:
=GEOMEAN(B3:B7)Result: 13.03
How to Fix Common GEOMEAN Errors in Google Sheets
You might encounter the #NUM! error when using GEOMEAN. Here’s why—and how to fix it.
Causes of #NUM! Error:
- A zero in the range
- A negative number in the range
The GEOMEAN function only works with positive numbers.
Solution 1 – Filter Out Non-Positive Values
=GEOMEAN(FILTER(B3:B7, B3:B7 > 0))Solution 2 – Convert Negatives to Positives Using ABS
echnically, the geometric mean is only defined for positive numbers. If your data contains negative values but you still want to proceed, you can use the ABS function to convert them to positive:
=GEOMEAN(FILTER(ABS(B3:B7), ABS(B3:B7) > 0))⚠️ Use this approach with caution, as it changes the meaning of the data.
That way, you can still include negative values by converting them before applying GEOMEAN.
When Should You Use Geometric Mean?
Use the geometric mean when:
- You’re working with growth rates (e.g., population, investments)
- You want to calculate the average ratio or percentage
- Values multiply over time, not add
Related Average Functions in Google Sheets
Looking for other ways to calculate averages? Check out these functions:
- How to Use the TRIMMEAN Function in Google Sheets
- How to Use the HARMEAN Function in Google Sheets
- AVERAGEIF Function for Conditional Averages in Google Sheets
- AVERAGEIFS Multiple Criteria Function in Google Sheets
- AVERAGE Function: Advanced Tips and Tricks in Google Sheets
- DAVERAGE Function in Google Sheets: Formula Examples
- AVERAGEA Function in Google Sheets – Formula Examples
- How to Calculate a Weighted Average in Google Sheets
