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

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