How to calculate geometric mean in Excel with negative numbers.

Geometric Mean is mostly used to calculate the average of the set of values, such as stock prices, investment returns, or growth rates. It is calculated by using the nth root of the product of n values. Calculating the geometric mean of positive numbers in Excel is straightforward and easy.

Here we have a dataset, in this dataset, there are five positive and negative numbers for which we have to find the geometric mean. In this tutorial, we will learn methods for calculating the Geometric Mean of negative numbers in excel but first let’s take a look at the dataset.

Method – 1 Using Geomean Formula

The GEOMEAN function can be used for calculating the Geometric mean of the absolute values of the numbers in the range.

Step – 1 Select the cell.

• Click on the cell where you want to write the formula.
• The syntax of the formula will be

=GEOMEAN(ABS(Start_Of_Range:End_Of_Range))

Start of range: Enter the cell address of the first cell of the range.

End of range: Enter the address of the last cell in the range.

ABS:The ABS function is used to take the absolute value of each number in the range. This is because the geometric mean formula requires all values to be positive.

• In our case the formula will be

=GEOMEAN(ABS(A2:A6))

Step – 2 Finding the Geometric mean.

• As we are going to use an array formula therefore, in order to enter this special formula, we have to press Ctrl + Shift + Enter. If you enter the formula like normal then you will get an error as shown below. This is valid for the versions of Excel 2013 to 2019 which supports legacy array formulas. However, from Office 2021 onwards, dynamic arrays were introduced, so you can directly enter the formula without pressing Ctrl + Shift + Enter.
• The geometric mean value will be calculated as shown above.

Method – 2 Using Product formula.

Step – 1 Select the cells.

• Select the cell where you want to write the formula.
• The syntax of the formula will be

=PRODUCT(Start_Of_Range:End_Of_Range)^(1/COUNT(Start_Of_Range:Start_Of_Range))

Start of range: You will enter the first cell of the range.

End of range: Enter the last cell of the range.

PRODUCT: This function is used to multiply all the numbers in the range together.

COUNT: The COUNT function is used for counting the number of the cells within the range.

1/COUNT(Start_Of_Range:Start_Of_Range): We take the reciprocal of the count, which gives us the power to which the product should be raised to get the geometric mean.

• In our case the formula will be

=PRODUCT(A2:A6)^(1/COUNT(A2:A6))

Step – 2 Finding the Geometric mean.

• Press the Enter key to enter the formula.
• The geometric mean will be calculated.