How to Calculate Average Deviation in Excel Formula

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will learn how to calculate average deviation in Excel. 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 this below.

How to Calculate Average Deviation in Excel

We want to determine how the costs are dispersed from the mean cost. Hence, we have to calculate the Mean Absolute Deviation.


How to Calculate Average Deviation in Excel Formula: 2 Useful Methods 

In this section, we will demonstrate 2 effective methods to calculate average deviation in Excel with appropriate illustrations.


1. Combine AVERAGE & ABS Functions to Calculate Average Deviation in Excel Formula

In this method, we will use two functions (AVERAGE & ABS functions) to determine the average deviations. Follow the steps below.

Steps:

  • First, let’s calculate the average cost per month. To do that, input the following formula in the cell.
=AVERAGE(C5:C16)

Using AVERAGE & ABS Functions to Calculate Average Deviation in Excel Formula

  • Now, we have calculated the average value. We have to calculate the absolute mean deviation which is |Monthly cost-Average|. To do that, input the following formula in cell D5.
=ABS($G$9-C5)

https://www.exceldemy.com/excel-abs-function/

  • Now, autofill the formula from cell D6 to D16 to calculate the deviation for other months. To do that, bring the mouse pointer to the bottom corner of cell D5. When you see the Plus (+) sign, drag it down to cell D16.

  • We will get the rest of the values for the months of February to December.

https://www.exceldemy.com/excel-abs-function/

  • And finally, we will calculate the average of the deviations by inputting the following formula in cell G11.
=AVERAGE(D5:D16)

https://www.exceldemy.com/excel-abs-function/

So, 290.82 is the average absolute deviation of the monthly grocery costs.

Read More: How to Calculate Average and Standard Deviation in Excel


2. Applying AVEDEV Function to Calculate Average Deviation in Excel Formula

The previous method is beneficial in understanding how the average absolute deviation is calculated step by step. But it was a bit lengthy and cumbersome. Excel has the AVEDEV function that can directly calculate the average absolute deviation. The steps to apply the formula are given below.

Steps:

  • In cell F9, write down the following formula.
=AVEDEV(C5:C16)

Applying AVEDEV Function to Calculate Average Deviation in Excel Formula

  • Click Enter, and you will see a similar result as the previous one which is 290.82.

Applying AVEDEV Function to Calculate Average Deviation in Excel Formula

Read More: How to Calculate Sum & Average with Excel Formula


Things to Remember

  • Using the AVEDEV function is the fastest way to calculate the Average Absolute Deviation.
  • If you find it hard to understand the inner calculations, you can go for the first method.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

This is the end of this article. If you find this helpful, please share it with your friends. Moreover, do let us know if you have any further queries.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Aniruddah Alam
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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo