HARMEAN Function in Excel: The Right Average for Rates

📎 This article includes 1 downloadable practice file ↓

StatisticsLevel: ExpertAvailable in: Excel 2007+

In this article

1. Syntax
2. Examples
3. Example 1
4. Example 2
5. HARMEAN in practice
6. Where you will use it
7. Worked example
8. Mistakes people make
9. Related functions
10. Continue learning

HARMEAN averages rates properly. Drive 60 km/h one way and 40 km/h back, and your average speed is 48, not 50.

Syntax

=HARMEAN(number1, [number2], ...)

Argument	What it means
number1…	Positive numbers.

Examples

Example 1

=HARMEAN(60, 40)

Returns 48.

Example 2

=HARMEAN(B2:B6)

Average P/E of equal-amount holdings.

HARMEAN in practice

Where you will use it

• Average speed over equal distances
• Average price per unit with equal spend
• Average of rates

Worked example

Speeds	Formula	Result
60, 40	=ROUND(HARMEAN(CHOOSE(ROW()-1,{60,40},{50,50},{30,60,90})),2)	48
50, 50	=ROUND(HARMEAN(CHOOSE(ROW()-1,{60,40},{50,50},{30,60,90})),2)	50
30, 60, 90	=ROUND(HARMEAN(CHOOSE(ROW()-1,{60,40},{50,50},{30,60,90})),2)	49.09

Results calculated in Excel.

Mistakes people make

Mistake	What to do instead
Using AVERAGE for speeds	Going 60 and returning 40 averages 48, not 50
Zero	#NUM!

Related functions

GEOMEAN · AVERAGE

📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.

Continue learning

All Statistical guides →

GAMMAINV Function in Excel: Inverse Gamma (Older Name)GAMMAINV returns the value at a gamma probability. It is a compatibility function: Excel…GAMMADIST Function in Excel: Gamma Distribution (Older Name)GAMMADIST returns the gamma probability. It is a compatibility function: Excel keeps it so…TTEST Function in Excel: t-Test (Older Name)TTEST returns the p-value of a t-test between two samples. It is a compatibility…GAMMA.INV Function in Excel: Value for a Gamma ProbabilityStatisticsLevel: ExpertAvailable in: Excel 2010+GAMMA.INV returns the value at a given gamma probability.Syntax=GAMMA.INV(probability, alpha.

📎 Practice files for this article

• 📗
  Practice workbook: Statistics advanced33 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.
  ⬇ XLSX · 36 KB

Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.