How to Calculate Mean Absolute Deviation in Excel

Q: What is the formula for Mean Absolute Deviation in Excel?

The simplest formula is =AVEDEV(range). If you want to write it manually, use the array formula =AVERAGE(ABS(range-AVERAGE(range))), which subtracts the mean from every data point, takes the absolute value of each difference, and averages the results. Both rely on Excel functions like AVERAGE and ABS.

To calculate Mean Absolute Deviation (MAD) in Excel, use the built-in =AVEDEV(range) function, which returns the average of the absolute deviations of your data points from their mean in a single step. If you want to see the math, you can also use the array formula =AVERAGE(ABS(range-AVERAGE(range))), which produces the identical result.

Hello and welcome to this informative blog post on how to calculate Mean Absolute Deviation in Excel. If you’re someone who has been working with data in Excel, you probably know how vital it is to analyze your data to extract insights that can help you make informed decisions. Mean Absolute Deviation is one such metric that helps you understand the amount of variation in a set of data points relative to their mean. In this blog post, we will guide you through the simple steps you can take to calculate Mean Absolute Deviation in Excel, so you can get a better understanding of your data and make informed decisions with confidence. Let’s dive in!

What is Mean Absolute Deviation?

Before diving into how to calculate Mean Absolute Deviation in Excel, let’s briefly define what it is. Mean Absolute Deviation (MAD) is a statistical measure that gives you an idea of how much your data set deviates, on average, from its mean. MAD is calculated by taking the absolute value of the difference between each data point and the mean, and then finding the mean of those absolute values.

The Fastest Way: The AVEDEV Function

Excel ships with a dedicated function for this exact calculation, so you rarely need to build it by hand. The AVEDEV function returns the mean absolute deviation of a set of values in one step:

=AVEDEV(range)

For example, if your data sits in cells A1 through A10, =AVEDEV(A1:A10) returns the MAD directly. AVEDEV computes the mean of your data, finds the absolute deviation of each point from that mean, and averages those deviations — all internally. You can pass it a cell range, individual numbers separated by commas, or a mix of both. Text and empty cells are ignored, while cells containing the number zero are counted.

The Manual Array Formula

If you prefer to keep the calculation transparent (or you want to confirm AVEDEV’s output), you can reproduce MAD with a single array formula that combines AVERAGE and the ABS function:

=AVERAGE(ABS(range-AVERAGE(range)))

So for data in A1:A10 you would enter =AVERAGE(ABS(A1:A10-AVERAGE(A1:A10))). In Excel 365 and Excel 2021, this spills automatically and returns the same value as AVEDEV. In older versions (Excel 2019 and earlier), confirm the formula with Ctrl+Shift+Enter so Excel evaluates it as an array; you’ll see it wrapped in curly braces like {=AVERAGE(ABS(A1:A10-AVERAGE(A1:A10)))}. This version is handy when you want to drop the MAD calculation into one cell without adding a helper column.

Worked Example with Numbers

Suppose you have five sales figures in cells A1:A5: 4, 8, 6, 5, 12.

Mean: =AVERAGE(A1:A5) returns (4 + 8 + 6 + 5 + 12) / 5 = 7.
Absolute deviations from the mean of 7: |4−7| = 3, |8−7| = 1, |6−7| = 1, |5−7| = 2, |12−7| = 5.
Average of those deviations: (3 + 1 + 1 + 2 + 5) / 5 = 12 / 5 = 2.4.

Both =AVEDEV(A1:A5) and =AVERAGE(ABS(A1:A5-AVERAGE(A1:A5))) return 2.4, confirming the manual walkthrough.

Step-by-Step Guide to Calculate Mean Absolute Deviation in Excel

If you would rather build the calculation column by column — which makes it easy to audit each step — follow this longer approach.

Step 1: Enter the Data into Excel

First, you need to enter the data into an Excel worksheet. Let’s say you have a sample data set with 10 data points, numbered from 1 to 10. In column A, you would enter the numbers 1 through 10 to represent your data points.

Step 2: Find the Mean

To find the mean of your data set, select a blank cell in your worksheet and use the AVERAGE function. For example, if your data points are in cells A1 through A10, you would use the formula =AVERAGE(A1:A10). Put the formula in a blank cell, and press Enter.

Step 3: Calculate the Absolute Deviation of Each Data Point

The absolute deviation of each data point is the absolute value of the difference between that data point and the mean. In other words, subtract the mean from each data point and take the absolute value of the result. For example, if your mean is 5.5 and your first data point is 2, then the absolute deviation of the first data point is |2-5.5| = 3.5.

To calculate the absolute deviation for each data point in your Excel worksheet, create a new column and label it “Absolute Deviation.” In the first row of the column, use the formula =ABS(A1-mean), where “mean” is the cell address of the mean value you calculated in step 2. Lock the mean’s cell reference with dollar signs (for example, =ABS(A1-$C$1)) so you can copy the formula down the whole column without the reference shifting.

Step 4: Find the Mean of the Absolute Deviations

Now, find the mean of the absolute deviations you just calculated. This is the Mean Absolute Deviation. In an empty cell, use the AVERAGE function to calculate the mean of the “Absolute Deviation” column.

You now know how to calculate Mean Absolute Deviation in Excel! This useful statistical measure allows you to better understand the amount of variability in your data set and make informed decisions based on that data. By following these simple steps, you can easily calculate the MAD of any data set in Excel.

How to Use MAD to Better Analyze Your Data

Mean Absolute Deviation is a powerful statistical measure that gives you valuable insights into the variability of your data set. You can use MAD to understand how much your data set deviates from the mean, which can help you identify outliers or unusual data points. This measure is especially useful when dealing with highly variable data sets where the standard deviation may not be the best indicator to represent the data.

MAD vs. Standard Deviation: When to Use Which

MAD is similar in many ways to standard deviation, which is another measure of variability commonly used in statistics. However, there are some important differences between the two measures. Standard deviation squares each deviation before averaging, which places greater weight on larger deviations from the mean, while MAD treats all deviations equally. Additionally, MAD is a more robust measure of variability, which means it is less sensitive to outliers in your data set.

So when should you reach for each?

Use MAD when you want an intuitive, plain-language measure of average spread, when your data contains outliers you don’t want to over-weight, or when you’re explaining variability to a non-technical audience. Because MAD is expressed in the same units as your data, it’s easy to interpret directly.
Use standard deviation when your data is approximately normally distributed, when you need a statistic that feeds into other procedures (confidence intervals, hypothesis tests, regression), or when larger deviations should count more. Standard deviation is the default in most statistical software for these reasons.

For a fuller comparison of spread statistics — including variance and kurtosis — see the broader overview of Excel statistics functions and the related Excel math functions guide.

Using MAD in Real-World Applications

MAD is a valuable metric that is widely used in research and statistical analysis. Some examples of how MAD can be used in real-world applications include:

Identifying stocks with low price volatility to help inform investment decisions
Measuring the variability of manufacturing processes to identify areas where improvement is needed
Detecting outliers in medical data that may indicate the need for further investigation

Mean Absolute Deviation is a straightforward yet powerful statistical measure that can help you unlock valuable insights hidden in your data sets. By calculating MAD in Excel and using it to better understand the variability of your data, you can make more informed decisions and drive better outcomes in any application where data analysis is necessary.

Troubleshooting

AVEDEV returns #DIV/0! — This happens when the range contains no numeric values (for example, it’s empty or holds only text). Make sure your range points at actual numbers.
AVEDEV returns 0 when you expected a positive number — Every value in the range is identical, so there is no deviation from the mean. This is mathematically correct.
The manual array formula returns a single wrong value in older Excel — In Excel 2019 and earlier you must confirm =AVERAGE(ABS(range-AVERAGE(range))) with Ctrl+Shift+Enter, not plain Enter, or Excel only evaluates the first cell of the range.
Text-formatted numbers are ignored — If some “numbers” are actually stored as text, AVEDEV skips them and your result is based on fewer points than you think. Convert them to real numbers first (multiply by 1, or use Text to Columns).
Hidden or filtered rows still count — AVEDEV and the array formula include hidden rows. If you need to exclude filtered-out data, calculate on a separate visible range or use SUBTOTAL-based logic.
Blank cells vs. zeros — Truly blank cells are ignored, but a cell containing 0 is treated as the value zero and will pull the mean and MAD accordingly.

Frequently Asked Questions

What is the AVEDEV function in Excel?

AVEDEV is Excel’s built-in function for mean absolute deviation. Written as =AVEDEV(range), it averages your numbers, measures how far each value falls from that average (using absolute values), and then returns the mean of those distances — the MAD — in a single formula.

What is the difference between Mean Absolute Deviation and Standard Deviation?

The main difference is in the way they treat deviations from the mean. Standard deviation squares the deviations, so it places more emphasis on larger ones, while Mean Absolute Deviation treats all deviations equally. Because of this, MAD is a more robust measure that is less sensitive to outlier data points than the standard deviation.

What is the formula for Mean Absolute Deviation in Excel?

The simplest formula is =AVEDEV(range). If you want to write it manually, use the array formula =AVERAGE(ABS(range-AVERAGE(range))), which subtracts the mean from every data point, takes the absolute value of each difference, and averages the results. Both rely on Excel functions like AVERAGE and ABS.

How do you interpret Mean Absolute Deviation?

MAD gives you an idea of how much your data deviates from the mean, on average, expressed in the same units as your data. A low MAD indicates that your data is clustered closely around the mean, while a high MAD indicates that there is more variability within your data set. MAD can also help you spot outliers or unusual data points that may require further investigation.

Can Mean Absolute Deviation be negative?

No, Mean Absolute Deviation cannot be negative. The absolute value used in the formula ensures that all deviations from the mean are converted to positive values, so the smallest possible MAD is zero (which occurs only when every value is identical).

When is it appropriate to use Mean Absolute Deviation?

Mean Absolute Deviation is a useful measure when dealing with highly variable data sets, particularly when the data set contains outliers or unusual data points. MAD is also beneficial when you want to understand how your data clusters around the mean, and it works well as an alternative to standard deviation when the data set is not normally distributed.

Does AVEDEV work the same in Excel and Google Sheets?

Yes. AVEDEV exists in both Excel and Google Sheets with identical syntax and behavior, so =AVEDEV(A1:A10) returns the same mean absolute deviation in either application.