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.

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)`

- 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)`

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

- And finally, we will calculate the average of the deviations by inputting the following formula in cell
**G11**.

`=AVERAGE(D5:D16)`

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)`

- Click
**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

**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

- 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
- How to Calculate Average of Top 5 Values in Excel
- How to Calculate Average from Different Sheets in Excel