AVEDEV Formula (MAD) in Excel & Google Sheets
Written by
Reviewed by
This tutorial demonstrates how to use the AVEDEV Function in Excel to calculate the average of the absolute value of deviations from the mean for a given set of data.
What is the AVEDEV Function?
The AVEDEV Function returns the mean absolute deviation of a data range: the average of the absolute distance of each score from the overall mean.
What is the Mean Absolute Deviation?
The mean absolute deviation, sometimes called the MAD, is a measure of dispersion – it tells you how spread out your data is.
Here’s why measures of dispersion are important. Take the following data set of five numbers:
48,49,50,51,52The mean of this data is 50 (calculated by adding all the numbers up, and then dividing by n, where n is the number of values in the set).
But now look at this data set:
10,25,50,75,90Again, the mean is 50 – but look how different the two ranges are! The first set us bunched up closely around the mean, the second is more spread apart.
The mean absolute deviation can give you an idea of this spread. You calculate it like this:
- Calculate the overall mean for the data range
- Calculate the absolute difference of each score from the mean (so any negative values are flipped to their positive equivalent)
- Take the average of these absolute differences
The Excel AVEDEV function does all of this for you in one cell.
How to use the AVEDEV Function
Use AVEDEV like this:
=AVEDEV(C4:C8)
AVEDEV returns 26.
You define just one argument with AVEDEV – the data range for which you want to calculate the mean absolute deviation.
AVEDEV will ignore any cells containing text or Boolean (TRUE/FALSE) values. Let’s add a few of these to the data range and see what happens:
=AVEDEV(C4:C12)
I’ve added four more rows to the table and expanded the data range in the AVEDEV function to include these cells. The result is the same: 26.
Note that cells containing 0 are counted as 0, and if your data range ONLY contains text and Booleans, you’ll get a #NUM! error.
Alternatives to AVEDEV
The mean absolute deviation can be useful, but is less commonly used than alternatives like the standard deviation or the variance. For more information on these alternatives, see the following tutorials:
AVEDEV Function in Google Sheets
The AVEDEV Function works exactly the same in Google Sheets as in Excel:
