GEOMEAN is a statistical (categorized) function for calculating the geometric mean of a dataset in Google Sheets.

Without using the GEOMEAN function we can calculate geometric mean in Google Sheets.

First of all, let me elaborate on that. So that you can quickly understand this one of the different types of average calculation.

Geometric Mean Calculation Using Custom Formulas in Google Sheets

Here are two examples of the geometric mean (average) calculation. I may use arithmetic operators and the circumflex/caret sign (^) for these calculations.

Example 1:

The geometric mean (a special middle number) of the numbers (just as an example) 5, 10, and 25 is the 3rd root of the product of these 3 numbers.

The product of the numbers is 5*10*25 which is equal to 1250. The third root (cubic root) of 1250 is =1250^(1/3) which is equal to 10.77.

If there are are more numbers 1/3 should be replaced by 1/n where ‘n’ is the count of numbers (nth root).

To know the detail about how to calculate nth root, check my guide – SQRT (Square Root), Cube Root and Nth Root in Google Sheets.

Example 2:

Calculate the geometric mean of the numbers 5, 10, 15, 20, and 25.

In a cell insert the following formula to find the product. The result would be 375000.

=5*10*15*20*25

There are 5 numbers in the population, right? So we must find the 5th root of 375000.

In another cell enter this formula to return the geometric mean of the above numbers 5, 10, 15, 20, 25 which would be 13.03.

=375000^(1/5)

The above geometric average calculation is simple with the GEOMEAN function in Google Sheets.

GEOMEAN Function Syntax and Formula Examples in Google Sheets

Syntax:

GEOMEAN(value1, [value2, ...])

Arguments:

value1 – The first value/range of the population.

value2 – Additional values/ranges to include.

How to use the GEOMEAN function in Google Sheets?

Here are two GEOMEAN formula examples.

=GEOMEAN(5,10,15,20,25)

Result: 13.03

=GEOMEAN(B3:B7)

Common Error in Google Sheets GEOMEAN Formulas and Reasons

When you use a range in the GEOMEAN, sometimes you may see #NUM! errors. Here are the possible reasons.

1. A negative number in the range.
2. 0 in the range.

To remove the #NUM! error in the GEOMEAN formula, that without deleting the numbers which cause the error, we can use the Filter function with GEOMEAN as below.

=GEOMEAN(filter(B3:B7,B3:B7>0))

I want to include negative numbers in the GEOMEAN calculation. What’s the solution?

Convert negative numbers to positive using ABS. In filtering, it will be as follows.

=GEOMEAN(filter(abs(B3:B7),abs(B3:B7)>0))

Average – Related Functions

1. How to Use the TRIMMEAN Function in Google Sheets.
2. How to Use the HARMEAN Function in Google Sheets.
3. Google Sheets Average Function [Advanced Tips and Tricks].
4. AVERAGE.WEIGHTED: Calculate the Weighted Average in Google Sheets.
5. DAVERAGE Function in Google Sheets – Formula Examples.
6. Average IF: Find Average Based on Condition in Google Sheets.
7. Averageifs Multiple Criteria Function in Google Sheets.