It is the average distance between the values and the mean. In this post, we are going to see how you can calculate mean and median absolute deviation (MAD) in excel. MAD is one of the ways to provide us with the measure of the dispersion of any set of data values.
Its values give us a clear-cut idea about when values are spread out and when it is close together.
Mean Absolute Deviation (MAD) in Excel
First, we will be going to see how you can calculate mean absolute deviation in excel.
Mean absolute deviation = (Σ |xi – x|) / n
Σ – means sum
xi – ith data value
x – mean value
n – sample size1. Enter Some Values
If you have any row data then you can add it to your excel. In my case, I am using some sample data. Here I added 20 data values from A2:A21.
2. Calculate the Mean Value
The next step is to calculate the Mean value of the given data set. You can use the =AVERAGE(A2:A21) formula to calculate the mean.
3. Find the Absolute Deviation
In order to find absolute deviation, we have to use the formula =ABS(A2-$D$2). After that, you can drop down the ‘+’ sign in the right corner to fill the remaining columns.
4. Find the Mean Absolute Deviation
Now the last step is to calculate the average of column B. The average of the column is nothing but the mean absolute deviation. For that, you can use the formula =AVERAGE(B2:B21).
In my case, the mean absolute deviation turns out to be 10.025. This method is applicable to large data sets as well. Here I have only assumed 20 data values but you can do it with any numbers.
Median Absolute Deviation (MAD) in Excel
Now we are going to see how to calculate median absolute deviation (mad) in excel. In this case, we first calculate the median and then subtract the data value from the median.
MAD = median(|xi – xm|)
where,
xi: ith value in the data set
xm: median value of data set1. Calculate The Median
We are using the same data set that we used earlier. Now our task is to find the median of that set. You can use the formula =MEDIAN(A2:A21) to calculate the median.
2. Find Out the Absolute Difference
The next step is to find out the absolute difference for each data value. For that, you need to use the formula =ABS(A2-$D$3).
3. Find Out the Median Absolute Deviation
It is the last step of this process. In order to calculate the median absolute deviation you have to calculate the median of “absolute differences”. You can use the formula =MEDIAN(B2:B21).
So guys this is all about how you can calculate the mean absolute deviation in excel as well as the median absolute deviation in excel. If you have any questions regarding this topic then don’t hesitate to comment down below.
