How to Use the AVEDEV Function in Microsoft Excel to Calculate Mean Absolute Deviation

When measuring statistical dispersion and data variability in a dataset, analysts and researchers most frequently reach for standard deviation (STDEV.S or STDEV.P) and variance (VAR.S). However, because standard deviation squares each deviation from the mean, it gives disproportionate weight to extreme outliers and skewed spikes. In financial risk modeling, quality control monitoring, and economic forecasting, squaring deviations can distort the perceived typical volatility of a system.

The Mean Absolute Deviation (MAD) offers an intuitive, robust alternative: it measures the average linear distance between each data point and the arithmetic mean. In Microsoft Excel, this calculation is built directly into the native AVEDEV function. With AVEDEV, you can measure dispersion without artificial outlier distortion. This tutorial explains the mathematics behind the function, its syntax and arguments, practical comparison against standard deviation, and common implementation errors.

Mathematical Definition of AVEDEV

The AVEDEV function evaluates the arithmetic average of the absolute differences between each individual observation and the mean of the dataset. Mathematically, it is expressed as:

AVEDEV = (1 / n) * ∑ |x_i - μ|

Where:

• n is the count of numeric data points in the sample.
• x_i represents each individual observation.
• μ is the arithmetic mean of the entire group (calculated via AVERAGE).
• |x_i − μ| is the absolute value (ignoring negative signs) of each distance.

Function Syntax and Arguments

The syntax for the AVEDEV function is straightforward:

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

• number1 (Required): The first numeric value, cell reference, or array range for which you want to calculate mean absolute deviation.
• number2, … (Optional): Up to 255 additional numbers, ranges, or cell references.

Step-by-Step Example: Assessing Portfolio Price Volatility

Consider evaluating the weekly price fluctuations of a stock to assess consistency without letting one single quarterly earnings spike overstate risk:

1. Open a new Excel spreadsheet and input weekly closing prices into cells A2:A11:
   100, 102, 101, 103, 99, 102, 100, 104, 98, 125 (note the outlier value 125 in week 10).
2. Calculate the arithmetic average in cell C2:

   =AVERAGE(A2:A11)

   Result: 103.4
3. Calculate the Mean Absolute Deviation using AVEDEV in cell C3:

   =AVEDEV(A2:A11)

   Result: 4.56
4. Calculate the sample standard deviation in cell C4 for comparison:

   =STDEV.S(A2:A11)

   Result: 7.82

Notice that STDEV.S reports a volatility index of 7.82 due to the squared penalty on the single 125 outlier, whereas AVEDEV yields 4.56, representing the true linear average deviation across all observations.

AVEDEV vs. STDEV.S: When to Use Which

Selecting the appropriate metric depends on your analytical objectives:

Characteristic	AVEDEV (Mean Absolute Deviation)	STDEV.S (Sample Standard Deviation)
Mathematical Weight	Linear weight (all deviations treated equally)	Quadratic weight (squares magnify large deviations)
Sensitivity to Outliers	Low to moderate	Extremely high
Interpretability	Direct physical units (average distance from mean)	Root-mean-squared distance (less intuitive)
Statistical Theory	Used in robust statistics and forecasting error (MAD)	Assumes normal Gaussian distribution curve

Handling Text, Logical Values, and Errors

Keep these data-cleansing rules in mind when supplying ranges to AVEDEV:

• Ignored Elements: If a referenced range contains blank cells, text strings, or logical values (TRUE / FALSE), AVEDEV ignores them automatically without throwing an error.
• Direct Text Arguments: If you pass text or logical values directly inside the function arguments (for instance, =AVEDEV(10, 20, "text")), Excel returns a #VALUE! error.
• Division by Zero: If the supplied range contains zero numeric values or is entirely empty, AVEDEV returns a #NUM! error because the sample size n equals zero.