What Is GEOMEAN Google Sheets Function?
The GEOMEAN function calculates the geometric mean of a range of positive numbers. The GEOMEAN in Google Sheets is the average rate of return of a bunch of values, and it is used in finance or other areas that see statistical growth. The geometric mean is also used to calculate the average rate of change over time.
For example, to calculate the geometric mean of the five numbers in a range, we enter the GEOMEAN Google Sheets formula in cell B6, as shown below.
Key Takeaways
- THE GEOMEAN function in Google Sheets calculates the geometric mean of positive values. In Google Sheets, you can use the GEOMEAN function to calculate the geometric mean of a set of values.
- It helps find the growth rate of a population, a product, a stock, or a value where the values grow exponentially.
- The syntax is as follows: =GEOMEAN(value1, [value2, …]). Here, the values represent numbers or cell references.
- The GEOMEAN function will return an error if any of the numbers in the range are negative as the geometric mean of negative or zero values is undefined.
- GEOMEAN returns the geometric mean of a set of values, while the AVERAGE() function returns the arithmetic mean of the dataset.
Syntax
Let us look at the syntax of GEOMEAN in Google Sheets.
The GEOMEAN function formula is as follows:
=GEOMEAN(number1, [number2], ….)
Arguments
- Number1, number2, …: The first number is mandatory while the rest of the numbers are optional
The arguments can be numbers, arrays, or cell references. If a cell contains strings or null values, the GEOMEAN function ignores it. You can input up to 30 values separated by commas or use a range of cells.
How To Use GEOMEAN Google Sheets Function?
The GEOMEAN function calculates the geometric mean of a given set of values. It is used in finance to calculate the average growth rate or returns given compounding variable rates. There are two ways to enter the function in Google Sheets.
Entering GEOMEAN Google Sheets Function Manually
Let’s examine how to enter the function manually. Below is a table containing a list of numbers.
Step 1: To find the geometric mean of the numbers, enter the following formula in cell A10.
=GEOMEAN(A2:A9)
Step 2: Press Enter. You get the geometric mean value of the numbers listed in Column A.
Thus, the calculation is simple and can be used in many real-time scenarios to measure the geometric mean.
Entering GEOMEAN through the Google Menu Bar
You can also access the GEOMEAN in Google Sheets through the menu bar.
Step 1: Click on the Insert tab, followed by Function. Here, click on the arrow in Statistical and select GEOMEAN from the list of functions provided.
Thus, the GEOMEAN can effectively calculate the geometric mean of a series of values.
Examples
The GEOMEAN in Google Sheets works by multiplying all the numbers together and then taking the nth root. Below are some examples that show how to use the GEOMEAN function.
Example #1 – Investment Returns Over Multiple Years
A man owns some gold whose value keeps growing yearly. The initial value of the gold is $60,000. In the first year, the value grows to $72,000, in the second year to $82,000, and in the third year to $90,000. We can find the growth rate over these using the GEOMEAN function. Enter the details in a Google Sheet.
Step 1: Cell B1 contains the gold value, while B3:B5 contains the yearly values. To calculate the average annual return and find the growth over three years, you can use the following formula.
=GEOMEAN(B4/B3, B5/B4) – 1
Step 2: Press Enter Press Enter. The compounded growth rate over three years is 0.118.
To express the result as a percentage, multiply by 100. Thus, the growth rate is 11.8%.
Here,
- B4/B3 gives the return from the first year to the second year.
- B5/B4 gives the return from the second year to the third year.
- GEOMEAN(…) calculates the geometric mean of these two.
- We subtract by 1 to get the growth rate.
Thus, the GEOMEAN function is a valuable tool for calculating the growth rate for such examples.
Example #2 – Population Growth Over Time
We have some details of the population growth over an area across decades. Let us use GEOMEAN function in Google Sheets to find the growth rate. Below is the table.
Step 1: To find the increase in population, we use the following formula in cell C2.
=B3-B2. Press Enter and drag the formula till C6.
Step 3: Now, to calculate the growth rate of each year, use the following formula.
=(C3/B2)*100 in cell D2.
Drag the formula till D6.
Step 3: Now, to find the geometric mean growth rate, use the following formula
=GEOMEAN(D3:D6)
Press Enter. You get 25%. We use this method for developing towns where the population growth rate is proportional to the current one.
Thus, we calculated the growth rate accurately, using the GEOMEAN in Google Sheets. The result will show the average annual return of your population over the decades as a percentage.
Example #3 – Product Price Changes Over Time
The geometric mean is usually calculated, whereas the arithmetic mean is inaccurate due to varied rates. To calculate the average annual growth rate using the GEOMEAN function, we calculate the year-over-year ratios. Here, we find the geometric mean of these ratios. In this example, let’s assume the product’s price over five years is as follows:
Step 1: Column A contains the years, and Column B contains the product values in $; Column C calculates the growth rate for each year.
Growth rate=(Price in current year/Price in previous year)−1.
Enter the =(B2/B1)-1in cell C2. Now, drag till C6.
Step 2: Now, calculate the average annual growth rate using the GEOMEAN function as follows:
=GEOMEAN(C2:C5)
This approach is more focused on the average growth rate with respect to the actual price changes each year.
Important Things to Note
- You can calculate the GEOMEAN only for positive values. Hence, if there is a negative number, GEOMEAN returns the #NUM Error.
- The arithmetic mean is the average of a data set with independent numbers. The geometric mean is used to calculate the mean when there is a compounding return in the dataset values.
- We can calculate the compounding interest returns with the geometric mean while the Arithmetic Mean calculates the simple interest returns.
- Text values are ignored if they are in the GEOMEAN function references. However, if you directly supply a text value as an argument, you get a #VALUE! Error.
- The arithmetic mean is calculated by adding and then dividing the values by their count and the geometric mean multiplies the values and then finds the root.
Frequently Asked Questions (FAQs)
The geometric mean is a type of mean used to calculate the mean in areas where the values are multiplied together or have an exponential growth pattern. This could be anything, from financial returns to population growth.
1. If you have data representing changing values over time, such as stock prices or product prices, the geometric mean helps find the average growth rate per period.
2. The GEOMEAN function is proper when the data is of multiplicative type rather than additive.
3. In finance, it is used to calculate the average return rate of an investment portfolio or the average yield of an asset over time.
• We get a #NUM! Error when a value is negative or zero value is present in the dataset.
• #DIV/0! Error occurs when the number of values passed to the GEOMEAN function is zero.
• It gives the #VALUE! Error when text values are passed as arguments.
The GEOMEAN function also ignores all blank spaces.
It is usually impossible to find the geometric mean of a data set with zero. However, you can navigate this issue by adding 1 to each value in the range, finding its GEOMEAN, and subtracting one from the result.
Yes, you can use the GEOMEAN function for values across multiple columns by their ranges. For example, to use the GEOMEAN formula for sets of values in columns A and B, we use the following formula:
=GEOMEAN(A1:A8, B1:B8)
You cannot calculate a geometric mean for a set that has negative numbers. To use negative numbers in the GEOMEAN formula, first convert them to a proportion. For example, a -20% growth rate should be represented as 0.80 ( 1 – 0.20). Negative values should be converted to their corresponding proportions and made positive before applying the GEOMEAN in Google Sheets.
Download Template
This article must help understand GEOMEAN in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is GEOMEAN in Google Sheets. We learn its syntax & how to use it to find the geometric mean with step-wise examples. You can learn more from the following articles. –
Dynamic Chart In Google Sheets
Leave a Reply