How to use the GEOMEAN function

What is the GEOMEAN function?

The GEOMEAN function lets you calculate the geometric mean in Excel. The geometric mean is a measure of the average rate of change.

How is the geometric mean calculated?

It is calculated by multiplying the numbers together and then taking the nth root where n is the number of values.

For example, calculating the geometric mean from numbers 5 and 20 equals 10.

Here is how: 5*20 equals 100. The root of 100 is 10.

To calculate the geometric mean by hand use this formula:

GM - geometric mean
y1, y2, ..., yn - are the numbers
n is the number of values

What is the difference between the geometric mean and the arithmetic mean (average)?

The geometric mean and arithmetic mean differ in how they are calculated. The geometric mean multiplies all the values together and then taking the n-th root, while the arithmetic mean adds all the values together and then divides by the number of values.

The AVERAGE function lets you calculate the arithmetic mean in Excel.

When to calculate the geometric mean?

The geometric mean is often a useful measure for data that increases or decreases rapidly like exponential or proportional numbers. The larger numbers skew the arithmetic mean while the geometric mean tends to better represent smaller values.

1. GEOMEAN Function Syntax

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

2. GEOMEAN Function Arguments

Argument	Description
number1	Required. A single numerical value or a cell reference to multiple numerical values.
[number2]	Optional. Upp to 254 additional arguments.

Text, logical values and empty cells are ignored.

The GEOMEAN function returns

• #NUM! error value if number is less than or equal to 0 (zero).

3. GEOMEAN Function Example

This example demonstrates how the geometric mean and the arithmetic mean can differ and show completely different results.

The image above shows five test scores for three students (Sophia, Emma, and Eric) in cells C3:G5, the arithmetic mean is calculated in cells I3:I5 and the geometric means are calculated in cells J3:J5.

Each test is equally important, however, tests 3 and 4 give much higher score than the other tests. How do we calculate an average test score that isn't distorted by test 3 and 4?

The arithmetic mean (average) tells us that Emma had the highest average test score. She had much better results in test 3 and 4, however, lower scores in test 1, 2, and 5 than the others. This shows that those tests (3 and 4) skew the arithmetic mean, the geometric mean is a better representation of the overall performance.

Formula in cell J3:

Eric has the highest geometric mean, he performed better than the other students in 3 out of 5 tests. In fact, Emma had the lowest geometric mean.

Back to top

4. Sort rows based on geometric mean

This example demonstrates an Excel 365 dynamic array formula in cell B8 that sorts rows in cell range B3:G5 based on the geometric mean values from large to small.

Excel 365 formula in cell B8:

Explaining formula

Step 1 - Calculate geometric mean

The GEOMEAN function calculates the geometric mean.

Function syntax: GEOMEAN(number1, [number2], ...)

GEOMEAN(a)

Step 2 - Build the LAMBDA function

The LAMBDA function build custom functions without VBA, macros or javascript.

Function syntax: LAMBDA([parameter1, parameter2, …,] calculation)

LAMBDA(a,GEOMEAN(a))

Step 3 - Calculate geometric mean by row

The BYROW function puts values from an array into a LAMBDA function row-wise.

Function syntax: BYROW(array, lambda(array, calculation))

BYROW(C3:G5,LAMBDA(a,GEOMEAN(a)))

returns

{118.608736627981; 101.832590045365; 144.500087439159}

Step 4 - Sort rows based on geometric mean

The SORTBY function sorts a cell range or array based on values in a corresponding range or array.

Function syntax: SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)

SORTBY(B3:G5,BYROW(C3:G5,LAMBDA(a,GEOMEAN(a))),-1)

becomes

SORTBY(B3:G5,{118.608736627981; 101.832590045365; 144.500087439159},-1)

and returns

Back to top

5. Sort columns based on geometric mean

The image above shows an Excel 365 dynamic array formula in cell C7 that sorts columns in cell range C2:G5 based on the geometric mean values from small to large.

Excel 365 formula in cell B8:

Explaining formula

Step 1 - Calculate geometric mean

The GEOMEAN function calculates the geometric mean.

Function syntax: GEOMEAN(number1, [number2], ...)

GEOMEAN(a)

Step 2 - Build the LAMBDA function

The LAMBDA function build custom functions without VBA, macros or javascript.

Function syntax: LAMBDA([parameter1, parameter2, …,] calculation)

LAMBDA(a,GEOMEAN(a))

Step 3 - Calculate geometric mean by column

The BYCOL function passes all values in a column based on an array to a LAMBDA function, the LAMBDA function calculates new values based on a formula you specify.

Function syntax: BYCOL(array, lambda(array, calculation))

BYCOL(C3:G5,LAMBDA(a,GEOMEAN(a)))

returns

{10,112.924323465723,427.19738509951,714.175292454516,73.4342046204996}

Step 4 - Sort rows based on geometric mean

The SORTBY function sorts a cell range or array based on values in a corresponding range or array.

Function syntax: SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)

SORTBY(B3:G5,BYCOL(C3:G5,LAMBDA(a,GEOMEAN(a))),1)

becomes

SORTBY(B3:G5,{10,112.924323465723,427.19738509951,714.175292454516,73.4342046204996},1)

and returns

Back to top

Back to top

Functions in 'Statistical' category

The GEOMEAN function function is one of 74 functions in the 'Statistical' category.

Excel function categories

Excel categories

Leave a Comment