Table of Contents
Calculating the Mean Absolute Deviation (MAD) in Excel can be done easily with a few simple steps. First, collect the data points for which you want to find the MAD. Then, use the built-in function, “AVERAGE,” to find the mean of the data set. Next, use the function “ABS” to calculate the absolute value of each data point minus the mean. After that, use the function “AVERAGE” again to find the mean of the absolute values. This will give you the MAD for your data set. By following these steps, you can easily calculate the MAD in Excel without the need for complex formulas or calculations.
Easily Calculate the Mean Absolute Deviation in Excel
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
- n – 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.