How To Use GEOMEAN Function in Google Sheets

The GEOMEAN function in Google Sheets is useful when you would like to calculate the geometric mean of a dataset.

The geometric mean is the nth root of the product of all the numbers in a set, where n is the size of the population. In finance, it is commonly used to determine the performance of an investment or portfolio.

The rules for using the GEOMEAN function in Google Sheets are as follows:

  • The function requires 1 or more values or ranges.
  • The function then calculates the geometric mean of the set of values.

Let’s begin with a quick use case where we can apply the GEOMEAN function.

Let’s say we have a spreadsheet with our month-on-month projected returns for the following year. We would like to return the average rate of return per month. However, since the interest is compounded, it’s not enough to find the average or arithmetic mean. 

Simple interest is represented by the arithmetic mean, while compounding interest is represented by the geometric mean. As we will demonstrate later, there is a slight difference between the output of both means. The geometric mean, however, is the more accurate measurement when handling data such as investment portfolios.

The GEOMEAN function will allow us to easily find the geometric mean of any range or array of values. This use case is just one way to use the function in Google Sheets. Geometric means are also used for highly volatile assets since the formula takes into consideration multiple compounding periods.

Let’s learn how to set up the GEOMEAN function ourselves in Google Sheets and later test out the function with sample financial values.

 

 

The Anatomy of the GEOMEAN Function

The syntax of the GEOMEAN function is as follows:

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

Let’s take apart the formula above and understand what each of these terms means: 

  • = the equal sign is how we start a function in Google Sheets.
  • GEOMEAN() is our GEOMEAN function. It computes the geometric mean of the provided dataset.
  • value1 refers to the first value or range of the dataset.
  • value2 and so on are additional values or ranges to include in our dataset.

 

 

A Real Example of Using GEOMEAN Function

Let’s look at a real example of the GEOMEAN function being used in a Google Sheets spreadsheet.

In the example below, we start with an initial balance of $5000. As you can see, each month has a certain interest which is computed in the third column. After computing the interest of each month, we now compare the difference between the arithmetic and geometric mean. 

We were able to return the final balance of $7500 using the geometric mean, while the arithmetic mean gives a slightly higher number.

Using the GEOMEAN Function in Google Sheets to find the rate of return of an investment

 

To get the geometric mean in the example above, we just need to use the following formula:

=GEOMEAN(C3:C14)

You can make your own copy of the spreadsheet above using the link attached below. 

If you’re ready to try out the GEOMEAN function in Google Sheets, let’s begin writing it ourselves!

 

 

How to Use GEOMEAN Function in Google Sheets

In this section, we will go through each step needed to start using the GEOMEAN function in Google Sheets. This guide will show you how to use the geometric mean to get the monthly rate of return for the sample business.

Follow these steps to start using the GEOMEAN function:

  1. First, let’s select the cell which will hold the result of our GEOMEAN function. In this example, we’ll place our geometric mean in cell A17.
    Select the cell to place our GEOMEAN Function in Google Sheets

  2. Next, we just have to type the equal sign ‘=‘ to begin our function, followed by ‘GEOMEAN(‘. 
  3. You may find a tooltip box with information on how to use the GEOMEAN function. We can click on the arrow symbol on the top-right-hand corner of the box to hide it if necessary.
    typing geomean function on formula bar

  4. Next, we’ll have to add in our arguments. Since we only want the return for the current year, we’ll start the range at C4 until the final rate for December in C14. Simply hit Enter on your keyboard to let the function evaluate.
    finding the geometric mean of a range


    In this example, we find out that the monthly rate of return is about 1.02.
    geometric mean gives a closer result than the arithmetic mean

 

Frequently Asked Questions (FAQ)

  1. When is it preferred to use the geometric mean over the AVERAGE formula?
    The geometric mean is often used when the values in the dataset follow a serial correlation. This is usually the case for data such as investment portfolios, where the price the previous day affects the current price to some degree. If your data does not follow such a pattern, it’s best to stick with the arithmetic mean used by the AVERAGE function. 

This step-by-step guide shows how easy it is to find the geometric mean of a dataset. The GEOMEAN function is just one example of a mathematical function you can use in Google Sheets. With so many other Google Sheets functions out there, you can surely find one that suits your needs.

Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like