AVEDEV Function

Returns the average absolute distance of numbers from their mean.

Category

Statistical

Available in

All versions, Microsoft 365

Returns

A number

Want to practice AVEDEV in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

AVEDEV finds the average, measures how far each number is from it (ignoring whether above or below), then averages those distances.

It answers "how far off is a typical value?" in the data's own units, and is easier to explain than standard deviation. Outliers affect it less, because distances aren't squared.

Syntax

=AVEDEV(number1, [number2], …)

Arguments

ArgumentRequired?What it does
number1YesA number, cell or range.
number2, …NoUp to 254 more numbers, cells or ranges.

Examples

Example 1 — Typical distance from the average

On average, each rep's sales are this far from the $53,636 mean.

E2fx=AVEDEV(C2:C8)
ABCDE
1RepRegionSalesMeasureResult
2AnaNorth62000Avg deviation7926.53
3BenSouth48500Std deviation9567.31
4CyNorth50000
5DiEast71250
6EdSouth39800
7FayNorth55400
8GusEast48500
=AVEDEV(C2:C8) Explain

It's smaller than the standard deviation, which gives extra weight to the far-away values such as Di's.

Common mistakes

  • #NUM! The range contains no numbers.
  • Text and blanks are skipped. Zeros count, so a 0 standing in for missing data increases the result.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.