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.

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

**Table of Contents**hide

**Download Practice Workbook**

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

**2 Useful Methods to Calculate Average Deviation in Excel Formula**

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 utilize two functions ( **the AVERAGE** & **the 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 that 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

`=ABS($G$9-C5)`

- Now, autofill the formula from
**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**+**sign, drag it down to**D16.**

- Consequently, 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. Fortunately, Excel has a built-in function, **AVEDEV** 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 Average, Minimum And Maximum in Excel (4 Easy Ways)**

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

**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. Finally, please visit **Exeldemy** for more exciting articles on **Excel**.

## Related Articles

**Average a Column in Excel (7 Easy Methods)****How to Calculate Average Only for Cells with Values in Excel****Calculate Average of Multiple Columns in Excel (6 Methods)****How to Exclude a Cell in Excel AVERAGE Formula (4 Methods)****[Fixed!] AVERAGE Formula Not Working in Excel (6 Solutions)****Calculate the Average of an Array with VBA (Macro, UDF, and UserForm)****How to Calculate Average Percentage Change in Excel (3 Simple Ways)**