How do you calculate mean deviation in Excel?
Mean Deviation, also known as Mean Absolute Deviation (MAD), is a statistical measure used to quantify the average distance between each data point in a dataset and the mean (average) of the dataset. It provides insights into the spread or dispersion of data points around the mean. Here's a comprehensive guide on how to calculate Mean Deviation in Excel:
Step-by-Step Guide:
Data Preparation:Arrange your data in a column in an Excel worksheet.
Calculate the Mean (Average):In a cell, use the
AVERAGEfunction to calculate the mean of the data. For example, if your data is in column A from A1 to A10, you would use:=AVERAGE(A1:A10)Calculate Absolute Deviations:In the next column, calculate the absolute deviations for each data point by subtracting the mean from each data point and taking the absolute value. Use the
ABSfunction. For example, if your mean is in cell B1 and your data is in column A from A1 to A10, you would use the following formula in cell C1 and then copy it down to C10:=ABS(A1-B1)Calculate Mean Deviation:After calculating the absolute deviations in column C, use the
AVERAGEfunction to calculate the mean of the absolute deviations. For example, if your absolute deviations are in column C from C1 to C10, you would use:=AVERAGE(C1:C10)
Excel Example:Let's say your data is in cells A1 to A10, and you want to calculate the Mean Deviation:
| Data | Mean Deviation | |
|---|---|---|
| 10 | ||
| 15 | ||
| 12 | ||
| 18 | ||
| 20 | ||
| 8 | ||
| 14 | ||
| 16 | ||
| 11 | ||
| 9 |
In cell B1, calculate the mean:
=B1 = AVERAGE(A1:A10)
In column C, calculate the absolute deviations:
C1 = ABS(A1-B1)
C2 = ABS(A2-B1)
...
In cell D1, calculate the Mean Deviation:
=D1 = AVERAGE(C1:C10)
Interpretation:The Mean Deviation gives you an idea of the average distance of data points from the mean. A lower Mean Deviation indicates that data points are closer to the mean, while a higher Mean Deviation suggests greater variability.
Remember that while Mean Deviation is a useful measure of dispersion, it doesn't take into account the squared differences like the more commonly used Standard Deviation does. Depending on your analysis needs, you might choose to use either Mean Deviation or Standard Deviation.
