HARMEAN function
HARMEAN returns the harmonic mean of positive numbers, which averages rates such as speeds or price-to-earnings ratios correctly by using reciprocals. It is never higher than the ordinary mean.
Example
Read it as a sentence: find the harmonic mean of the numbers in A1:A8, which gives 4.201750729.
Excel shows 4.201750729
A
1
2
2
4
3
4
4
4
5
5
6
5
7
7
8
9
Data
Syntax
HARMEAN(number1, [number2], ...)
Arguments in [square brackets] are optional.
-
number1(required) - First number, array or range.
-
number2(optional) - Further numbers or ranges.
Returns: The harmonic mean of the positive numbers.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=HARMEAN({2,4,4,4,5,5,7,9}) | 4.201750729 | Array constant |
=HARMEAN(10,"20",TRUE) | 2.608695652 | Direct args: number, text-number, TRUE |
=HARMEAN(5) | 5 | Single number |
=HARMEAN({1,"2",TRUE,3}) | 1.5 | Array with text and boolean |
=HARMEAN(Data!A1:A8) | 4.201750729 | Range of numbers |
=HARMEAN(Data!B1:B8) | 18.94736842 | Mixed range: num, text-number, TRUE, blank, text, FALSE |
=HARMEAN(10,"abc") | #VALUE! | Direct arg: non-numeric text |
=HARMEAN({0,2}) | #NUM! | Contains zero |
The Data sheet these formulas read
A cell that starts with = holds that formula.
A
B
1
2
10
2
4
20
3
4
TRUE
4
4
5
5
abc
6
5
30
7
7
FALSE
8
9
40
Data
Watch out for
- All values must be above zero.
- Always lower than or equal to the ordinary mean.
Where it works
- Excel
- HARMEAN is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Statistical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .