Return to List of Excel Functions

AVEDEV Formula (MAD) in Excel & Google Sheets

This Tutorial demonstrates how to use the Excel AVEDEV Function in Excel to returns the average of the absolute value of deviations from the mean for a given set of data.

AVEDEV Main Function

AVEDEV Function Overview

The AVEDEV Function Calculates the average of the absolute value of deviations from the mean for a given set of data.

To use the AVEDEV Excel Worksheet Function, select a cell and type:

AVEDEV Function

AVEDEV function Syntax and inputs:

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

number1 – First value or reference.

number2 – [optional] Second value or reference.


What is the AVEDEV Function?

The Excel 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 that alternatives like the standard deviation or the variance. For more information on these alternatives, see the following pages:

  • How to calculate the standard deviation in Excel<<link>>
  • How to calculate the variance in Excel<<link>>


Easy Excel Automation

Add Excel automation to your workbook with just a few clicks.

Learn More

AVEDEV Function in Google Sheets

The AVEDEV Function works exactly the same in Google Sheets as in Excel:

AVEDEV Google Function

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