How spread out is your data, on average? AVEDEV returns the mean of the absolute distances from the average — a simple, outlier-resistant measure of variability that’s easier to explain than standard deviation.
The example
Average distance from the mean.
| A | B | |
|---|---|---|
| 1 | Value | MAD |
| 2 | 2, 4, 6, 8 | 2 |
The formula
The formula:
How it works
How it works:
AVEDEV(range)averages the absolute deviations from the mean — |value − average|.- Unlike standard deviation, it doesn’t square the differences, so outliers pull it less.
- It’s in the same units as the data, making it intuitive: “on average, points are X away from the mean.”
- Equivalent to
=SUMPRODUCT(ABS(range - AVERAGE(range)))/COUNT(range)if you want to see the mechanics.
MAD vs standard deviation: standard deviation squares deviations (so big errors dominate) and is the basis of most statistical tests; MAD treats all distances linearly and is more robust to outliers. For a quick, plain-language sense of spread, MAD is often the clearer choice.
Try it: interactive demo
Values.
Variations
By hand
The mechanics:
Standard deviation
Squared version:
Deviation from median
Robust center:
Pitfalls & errors
Not standard deviation. AVEDEV uses absolute, not squared, deviations — the values differ.
Mean-based. It measures spread around the mean; for skewed data, deviation from the median may be more telling.
Text is skipped. AVEDEV ignores text and blanks, like AVERAGE.
Practice workbook
Frequently asked questions
What does AVEDEV do in Excel?
How is MAD different from standard deviation?
Can I compute it without AVEDEV?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class