How to Use the GEOMEAN and HARMEAN Functions in Google Sheets for Financial and Ratio Averages

When calculating central tendencies across financial investments, population growth models, or speed-and-rate ratios, the standard arithmetic mean (AVERAGE) frequently distorts reality. For example, averaging investment returns of +50% and -50% using arithmetic mean yields 0%, whereas the actual portfolio has suffered a net capital loss of 25%. To calculate compounding growth and rate ratios accurately, Google Sheets includes two specialised statistical functions: GEOMEAN (Geometric Mean) and HARMEAN (Harmonic Mean).

When to Use Geometric vs Harmonic vs Arithmetic Mean

Selecting the correct averaging function depends on the nature of your underlying data:

• Arithmetic Mean (AVERAGE): Best for additive quantities and independent measurements (such as heights, temperatures, or item costs).
• Geometric Mean (GEOMEAN): Essential for compounding percentages, investment portfolio returns, inflation indices, and population growth rates where values multiply together over successive intervals.
• Harmonic Mean (HARMEAN): The mathematically precise measure for ratios, rates, and reciprocal speeds (such as miles per hour, price-to-earnings ratios, or network throughput times).

Function Syntax and Arguments

1. GEOMEAN Syntax

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

The geometric mean multiplies all \(n\) positive numbers together and extracts their \(n\)-th root:

\[ \text{GEOMEAN} = \sqrt[n]{x_1 \times x_2 \times \dots \times x_n} \]

Requirement: All arguments supplied to GEOMEAN must be strictly greater than zero (\(x > 0\)). Passing zero or negative values results in a #NUM! error.

2. HARMEAN Syntax

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

The harmonic mean calculates the reciprocal of the arithmetic mean of reciprocals:

\[ \text{HARMEAN} = \frac{n}{\sum_{i=1}^{n} \frac{1}{x_i}} \]

Requirement: All values must be strictly positive numbers. Any value \(\le 0\) produces a #NUM! error.

Real-World Example 1: Calculating Compound Investment Returns (GEOMEAN)

Consider an investment portfolio evaluated over three consecutive years with the following annual returns:

• Year 1: +20%
• Year 2: -10%
• Year 3: +30%

Step-by-Step Calculation:

1. Convert percentage changes into compounding growth factors (Gross Return Multipliers = \(1 + r\)):
   • Cell A2: 1.20 (\(1 + 0.20\))
   • Cell A3: 0.90 (\(1 – 0.10\))
   • Cell A4: 1.30 (\(1 + 0.30\))
2. Apply the GEOMEAN formula in cell B2:

   =GEOMEAN(A2:A4) - 1

3. Format cell B2 as a percentage. Google Sheets displays:

   12.00%

If you used =AVERAGE(0.20, -0.10, 0.30), it would report an exaggerated 13.33%, misrepresenting actual compound wealth generation.

Real-World Example 2: Averaging Speeds and Rates (HARMEAN)

Suppose a delivery vehicle drives 60 miles to a distribution depot at 60 mph, and then returns along the exact same 60-mile route during heavy traffic at 30 mph.

Using the arithmetic mean suggests an average speed of 45 mph \((60 + 30) / 2\). However, because more time was spent travelling at the lower speed, the actual average speed over the total 120-mile round trip is lower:

1. Enter the two speeds into cells C2 (60) and C3 (30).
2. Enter the HARMEAN formula in cell C4:

   =HARMEAN(C2:C3)

3. Google Sheets outputs:

   40

The vehicle’s true average speed is exactly 40 mph, matching the physical physics formula (\(\text{Total Distance} / \text{Total Time} = 120 / 3 = 40\)).

Handling Zero and Negative Values Safely

Because financial returns often drop into negative territory, passing them directly into GEOMEAN breaks calculations. Always convert percentages into positive growth factors (\(1 + \text{rate}\)) before evaluating.

If your dataset contains empty cells or text strings, both GEOMEAN and HARMEAN automatically ignore them within range arguments (e.g. A2:A20). However, literal text or booleans typed directly into formula arguments produce an error.