HARMEAN function

HARMEAN returns the harmonic mean of positive numbers, useful for averaging rates and ratios where reciprocal relationships matter.

=HARMEAN(number1, ...)

Generate a HARMEAN formula

Describe what you need. The generator will reach for HARMEAN where HARMEAN is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How HARMEAN reads its arguments
number1requiredHARMEAN →
ArgumentRequiredDescription
number1RequiredA required numeric value greater than zero. Can be an integer or decimal.
...RepeatingOptional additional numeric values, all must be positive (> 0). You can supply up to 255 values total.

Returns

A number representing the harmonic mean of all supplied values.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Find average budget-to-actual spending ratio across categories

CategoryBudgetedActual
Groceries400420
Utilities150145
Transport200180
=HARMEAN(400/420, 150/145, 200/180)

Result: ~1.029

This calculates budget-to-actual ratios for each category (0.952, 1.035, 1.111) and returns their harmonic mean of approximately 1.03. The result indicates you spent about 3% less than budgeted across categories on average. Harmonic mean is better here than AVERAGE because it weights efficiency ratios equally regardless of whether an expense category is $150 or $400—the ratio's accuracy matters more than the dollar amount.

2. Calculate average spending variance as a percentage of budget

CategoryBudgetedActualVariance
Groceries400420-20
Utilities1501455
Transport20018020
=HARMEAN(20/400, 5/150, 20/200)

Result: ~0.050 (5%)

This divides absolute variance by budget for each category, yielding rates of 5%, 3.3%, and 10%. The harmonic mean of these rates is approximately 5%. Using HARMEAN instead of AVERAGE prevents the 10% transportation variance from skewing the result upward—the harmonic mean properly balances all three variance rates regardless of their different budget sizes.

3. Compare average spending efficiency across two months

CategoryMonthBudgetedActual
GroceriesJan400420
UtilitiesJan150145
TransportJan200180
GroceriesFeb400385
UtilitiesFeb150160
TransportFeb200210
=HARMEAN(420/400, 145/150, 180/200, 385/400, 160/150, 210/200)

Result: ~0.996

This calculates actual-to-budget ratios for all six data points (1.05, 0.967, 0.9, 0.9625, 1.067, 1.05) and returns their harmonic mean of approximately 1.00. This shows overall spending stayed almost exactly on budget across both months. Harmonic mean ensures each category's efficiency ratio contributes equally to the final average, avoiding bias from large absolute dollar amounts.

Common errors

Which HARMEAN error are you seeing?
HARMEAN returned an error#DIV/0!
Ensure all values are strictly positive. For variance data that includes negative numbers, wrap your range with ABS(): =HARMEAN(ABS(A1:A5)). Alternatively, filter to only positive entries before passing to HARMEAN.
#VALUE!
Verify all cells in your range contain only numeric values. Remove text headers and labels. If cells are formatted like '$420 USD', extract just the numeric portion before using HARMEAN.
#NUM!
Check for data-entry errors like extra zeros in very large numbers. If all data is correct, try using AVERAGE or MEDIAN as an alternative, or scale your inputs down by dividing all values by a constant before passing to HARMEAN.
ErrorWhy it happensHow to fix it
#DIV/0!HARMEAN internally computes 1/value for each argument. If any value is zero or negative, the reciprocal operation fails, producing a division-by-zero error.Ensure all values are strictly positive. For variance data that includes negative numbers, wrap your range with ABS(): =HARMEAN(ABS(A1:A5)). Alternatively, filter to only positive entries before passing to HARMEAN.
#VALUE!One or more arguments contains text, a date, a logical value, or other non-numeric data that cannot be converted to a number.Verify all cells in your range contain only numeric values. Remove text headers and labels. If cells are formatted like '$420 USD', extract just the numeric portion before using HARMEAN.
#NUM!The harmonic mean calculation encounters a numerical error or overflow, typically when input values are extremely large or when computation precision limits are exceeded.Check for data-entry errors like extra zeros in very large numbers. If all data is correct, try using AVERAGE or MEDIAN as an alternative, or scale your inputs down by dividing all values by a constant before passing to HARMEAN.

Tips and when to use something else

  • Use HARMEAN for rates, speeds, efficiency ratios, and cost-per-unit measures—not for absolute counts like total expenses or inventory quantities.
  • HARMEAN always returns a value less than or equal to AVERAGE, and this is correct. It reflects the mathematical reality that low values matter more when averaging reciprocals.
  • For spending efficiency, HARMEAN of (Actual/Budgeted) ratios gives a fairer picture than AVERAGE because it treats a 20% overage in a $150 utility bill equally to a 20% overage in a $400 grocery budget.
  • If you need the middle value instead of a rate average, use MEDIAN(). If you need to count entries meeting a condition, use COUNTIF(). HARMEAN is specifically for averaging rate-based or reciprocal relationships.

Related functions

AVERAGEAVERAGE calculates the arithmetic mean of numbers, ignoring text and logical values; returns a single numeric value representing the average.MEDIANMEDIAN returns the middle value in a sorted list of numbers, or the average of the two middle values if the count is even.MODE.SNGLReturns the most commonly appearing numeric value in a dataset, ideal for identifying the most frequent priority level, rating, or resolution time.LARGELARGE returns the k-th largest value in an array, where k=1 is the maximum, k=2 is the second-largest, and so on.SMALLSMALL returns the k-th smallest value from an array, where k=1 gives the minimum and higher k values give progressively larger results.

Frequently asked questions

What is the difference between HARMEAN and AVERAGE?
HARMEAN calculates the average of reciprocals (1/x), making it ideal for rates and ratios. AVERAGE sums values and divides by count, treating all numbers equally. HARMEAN is mathematically correct when you're averaging rates where low values deserve more influence—for example, budget efficiency ratios or speed averages.
Why does HARMEAN give a different result than the arithmetic average?
HARMEAN uses reciprocals in its calculation, so it inherently produces a smaller or equal value. This is intentional—it properly weights rate data. For example, averaging speeds of 60 mph one way and 30 mph back is 40 mph by harmonic mean, not 45 mph, because you spend twice as long at the slower speed.
What happens if my data includes zero or negative values?
HARMEAN will return #DIV/0! error because the function requires all values to be strictly positive (> 0). To handle negative variance data, use ABS() to convert to absolute values: =HARMEAN(ABS(B2:B10)). You cannot include zero or negative numbers in any form.
Can I use HARMEAN with a range or array formula?
Yes. You can use a range like =HARMEAN(A1:A10), individual cells like =HARMEAN(A1, B1, C1), or array operations like =HARMEAN(A1:A5/B1:B5). HARMEAN accepts 1 to 255 arguments total. Using more values produces a more representative harmonic mean.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17