GEOMEAN Function in Excel – Geometric Mean

In the previous blog, we implemented the HARMEAN Function to get the value of Harmonic Mean. The AVERAGE Function returns the Arithmetic mean. In this blog, we are going to implement another essential statistical function, GEOMEAN Function in Excel.

So let’s get started.

When to Use the GEOMEAN Function of Excel?

The word “GEOMEAN” represents Geometric Mean. The GEOMEAN Function calculates the Geometric Mean for a data set containing positive numerical values. Geometric Mean is an important statistical parameter and its value is not the same as the Arithmetic mean.

The Value of the Geometric Mean is always less than the arithmetic mean. The GEOMEAN function can be used in Excel 2003 and later versions.

Syntax and Arguments

=GEOMEAN(number1,[number2]….)

The following notes contain important information about the inputs required by GEOMEAN Function in Excel.

  • number1 – This inputs a value or a range of values from the numerical data set whose Geometric Mean we are going to calculate.
  • [number2] – This is an optional input. However, it works the same as the number1 argument.

The GEOMEAN Formula can have a maximum of 255 number arguments in Excel 2007 and later versions while it has 30 number arguments in Excel 2003, Each input can either be a single numerical value or an array of values.

What is Geometric Mean?

Let us suppose we have the two sides of a rectangle as 4 and 16.

We want to make a square having the same area as that of the rectangle. This is what geometric mean would do for us!

geometric mean in excel

You can see that the rectangle having two different dimensions 4 and 16 have equal area to that of a square with side 8.

Therefore, the Geometric mean of 4 and 64 would be 8.

GM= √4*16 = √68= 8

This was a practical example of the Geometric mean for two values. We can have a formula of geometric mean for any number of values.

Let us suppose we have a data set containing n values as a,b,c,d,… up to n values.

Therefore the Geometric mean of the values is:

GM= (a.b.c…..up to n terms ) ^ (1/n)

The Geometric mean is the product of all numerical values raised to the inverse of n ( total number of values ). It is particularly used to get an average interest rate on money compounded annually.

infographics geometric mean function in excel

Examples to Use GEOMEAN Function in Excel

The GEOMEAN Function can have several applications as implemented in the examples below.

Example 1 – Passing Direct Numbers to GEOMEAN Function

Let us suppose that we want to get the geometric mean of the data set containing the following numerical values:-

{4,6,5,2,4,5}

Use any of the two GEOMEAN Excel formulas to get the GM for this data.

=GEOMEAN(4,6,5,2,4,5)

or

=GEOMEAN({4,6,5,2,4,5})
example to implement geomean function in excel

As a result, the formula returns the value of the Geometric mean as 4.11

Explanation – We have supplied the direct numerical values as number 1, number 2, number 3, number 4, number 5 and number 6 input in the first formula. In the alternate formula, we have combined the values in an array using the brackets as {4,6,5,2,4,5}. This array is supplied as the number1 argument of the GEOMEAN Function. In both the formulas, the function successfully evaluates the Geometric Mean.

Example 2 – Calculating Geometric Mean for Different Ranges

let us suppose we have our data set spilled in two ranges of Excel spreadsheet.

geomean function in excel examples

We want to get the geometric mean of the values contained in the ranges A2:A7 and C2:C7.

Use the following GEOMEAN Function formula.

=GEOMEAN(A2:A7,C2:C7)
geometric mean in excel result

As a result, we got the Geometric mean as 19.69

Explanation – We have supplied the two ranges A2:A7 and C2:C7 as the number1 and number2 arguments respectively.

Important Points about GEOMEAN Function

We have some important notes about GEOMEAN Formula as follows.

  • We can supply a single value or an array of values as the single input of the GEOMEAN Formula.
  • The function ignores the empty cells, logical values (TRUE and FALSE), and text strings when referenced by the function arguments.
  • Any negative value in the data set results in a #NUM! error.

This brought us to the end of the GEOMEAN Function blog.

Thank you for reading.

Leave a Comment