Average Deviation or Average Absolute Deviation (AAD) represents how closely or how far away all the data points are located from any reference value, such as the mean or median of the group of data. A small AAD indicates that the data points are close to the reference mean/ median value. On the other hand, a large AAD indicates that the data points are dispersed from the reference. The formula for Average Absolute Deviation is below:
Where
- xi is the ith data value,
- x is the mean value,
- n is the sample size.
Suppose, we have a list of grocery costs for each month of a year like the one below. We will determine how the costs are dispersed from the mean cost. Therefore, we have to calculate the Mean Absolute Deviation.
Method 1 – Combine AVERAGE & ABS Functions to Calculate Average Deviation in Excel Formula
Steps:
- We will first calculate the average cost per month. Enter the following formula in the desired cell.
=AVERAGE(C5:C16)
- We have calculated the average value. We have to calculate the absolute mean deviation which is |Monthly cost-Average|. To do that, enter the following formula in cell D5.
=ABS($G$9-C5)
- Autofill the formula from cell D6 to D16 to calculate the deviation for other months.
- We will get the rest of the values for the months of February to December.
- Enter the following formula in cell G11 to calculate the average of the deviations.
=AVERAGE(D5:D16)
290.82 is the average absolute deviation of the monthly grocery costs.
Read More: How to Calculate Average and Standard Deviation in Excel
Method 2 – Applying AVEDEV Function to Calculate Average Deviation in Excel Formula
Steps:
- In cell F9, enter the following formula.
=AVEDEV(C5:C16)
- Press Enter and you will see a similar result as the previous one which is 290.82.
Read More: How to Calculate Sum & Average with Excel Formula
Download Practice Workbook
Related Articles
- How to Do Subtotal Average in Excel
- How to Average Filtered Data in Excel
- How to Calculate Average of Text in Excel
- How to Calculate Average Excluding Outliers in Excel
- How to Average Negative and Positive Numbers in Excel
<< Go Back to Calculate Average in Excel | How to Calculate in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
