# How to Calculate Average Deviation in Excel Formula

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:

Average Absolute Deviation= (Σ |xi – x) / n

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.

### 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.

## Related Articles

<< Go Back to Calculate Average in Excel | How to Calculate in Excel

Get FREE Advanced Excel Exercises with Solutions!
Aniruddah Alam

Md. Aniruddah Alam, a graduate of Bangladesh University of Engineering & Technology, Dhaka, holds a BSc.Engg in Naval Architecture & Marine Engineering. Joining Softeko in August 2022 as an Excel and VBA content developer, he has authored 90+ articles, covering fundamental to advanced Excel topics. He actively develops VBA codes for Excel automation and offers solutions in the Exceldemy Forum. Beyond work, he finds leisure in reading books, and taking relaxing walks, showcasing well-rounded blend of professional and... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF