AVEDEV Formula (MAD) in Excel & Google Sheets

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on November 9, 2023

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.

AVEDEV Main Function

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:


The 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:


Again, 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:


How to use AVEDEV

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:


Text and Boolean

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:

AVEDEV Google Function

AI Formula Generator

Try for Free

Excel Practice Worksheet

practice excel worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!

Free Download

Return to List of Excel Functions