How to Easily Calculate the Mean Absolute Deviation in Excel

This tutorial explains how to calculate the mean absolute deviation for a set of data values in Excel.

What is the Mean Absolute Deviation?

The mean absolute deviation is a way to measure the dispersion for a set of data values.

A low value for the mean absolute deviation is an indication that the data values are concentrated closely together.

A high value for the mean absolute deviation is an indication that the data values are more spread out.

The formula to calculate the mean absolute deviation is as follows:

Mean absolute deviation = (Σ |xi – x|) / n

  • Σ – just a fancy symbol that means “sum”
  • xi – the ith data value
  • x – the mean value
  • – sample size

How to Calculate the Mean Absolute Deviation in Excel

To calculate the mean absolute deviation in Excel, we can perform the following steps:

Step 1: Enter the data. For this example, we’ll enter 15 data values in cells A2:A16.

Step 2: Find the mean value. In cell D1, type the following formula: =AVERAGE(A2:A16). This calculates the mean value for the data values, which turns out to be 15.8.

Step 3: Calculate the absolute deviations. In cell B2, type the following formula: =ABS(A2-$D$1). This calculates the absolute deviation of the value in cell A2 from the mean value in the dataset.

Next, click cell B2. Then, hover over the bottom right corner of the cell until a black sign appears. Double click the sign to fill in the remaining values in column B.

Step 4: Calculate the mean absolute deviation. In cell B17, type the following formula: =AVERAGE(B2:B16). This calculates the mean absolute deviation for the data values, which turns out to be 6.1866.

Note that you can use these four steps to calculate the mean absolute deviation for any number of data values. In this example, we used 15 data values but you could use these exact steps to calculate the mean absolute deviation for 5 data values or 5,000 data values.

Leave a Reply

Your email address will not be published. Required fields are marked *