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.

1. Introduction

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.

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

2. GEOMEAN Function Syntax

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

3. 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).

4. 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

5. 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.

The image above shows a data table containing test scores for three students: Sophia, Emma and Eric. The geometric mean is calculated for each row in cells I3,I4, and I5 respectively.

	Test 1	Test 2	Test 3	Test 4	Test 5		Geometric mean
Sophia	10	120	405	805	60		119
Emma	5	80	550	905	55		102
Eric	20	150	350	500	120		145

The Excel 365 formula in cell B8 dynamically calculates the geometric mean for each student and returns a sorted list based the geometric mean. For example, Eric has the highest geometric mean score and his name, test scores and corresponding geometric mean is displayed in the first row.

The second highest score has Sophia and her name, test scores, and geometric mean is shown in the second row. The third highest score has Emma and here data is shown in the third row in the array output.

Excel 365 formula in cell B8:

The formula returns a dynamic array meaning it spills values to the right and to cells below as far as needed.

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

6. 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.

The image above shows a data table containing test scores for three students: Sophia, Emma and Eric. The geometric mean is calculated for each column in cells C12:G12 respectively.

	Test 1	Test 2	Test 3	Test 4	Test 5
Sophia	10	120	405	805	60
Emma	5	80	550	905	55
Eric	20	150	350	500	120

The Excel 365 formula in cell C7 dynamically calculates the geometric mean for each test and returns a sorted list based on the geometric mean from small to large. For example, Test 1 has the lowest geometric mean score and is displayed in the first column. Test 5 is the second column meaning it has the second smallest geometric mean score etc.

Excel 365 formula in cell C7:

The formula returns a dynamic array meaning it spills values to the right and to cells below as far as needed.

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 73 functions in the 'Statistical' category.

Excel function categories

Excel categories

Leave a Comment