# How to Calculate Absolute Deviation in Excel (2 Useful Examples)

Get FREE Advanced Excel Exercises with Solutions!

Absolute Deviation is a useful tool that indicates the variability of a dataset. It gives a sense of idea about a dataset whether it is clustered or spread or dispersed. This article will show you how you can calculate Absolute Deviation in an Excel worksheet. ## 2 Examples to Calculate Absolute Deviation in Excel

We are going to show you two examples of calculating Absolute Deviation in Excel. The first one is calculating Mean Absolute Deviation and the other one is Calculating Median Absolute Deviation. Both of these two terms are well known in statistical analysis.

### 1. Calculating Mean Absolute Deviation

Mean Absolute Deviation is the average of Absolute Deviation. The formula for the Mean Absolute Deviation is given as: In this formula,

• X = Observation Value
• µ = Average
• N = Number of Observation

We can perform this calculation in two ways. First, we will calculate mathematically and then we will use an in-built Excel function AVEDEV.

#### 1.1 Mean Absolute Deviation by Mathematical Formula

In this Procedure we have to use the AVERAGE and ABS functions. First, we have taken a dataset that includes several Student ID and  Total Number that they have got. After that, we have to modify the data table according to our requirements.

• Allocate a cell where you will find the arithmetic Average and a column for Absolute Deviation values.
• Also, you need a cell to calculate the Mean Absolute Deviation.

You can follow the below data table. To find the arithmetic average,

• Write down the following formula in D5 and Press ENTER.

`=AVERAGE(C5:C14)`

This function calculates the average of any given data range. • So, we have the arithmetic average which is 535.5. • Now, we have to calculate the Absolute Deviations for each observation.
• So, write down the following formula in E5 and Press ENTER.

`=ABS(C5-\$D\$5)`

The function returns the absolute (i.e. positive) value of a given number. • Thus, we have got Absolute Deviation for the first pair.
• Now, Hold and Drag the E5 cell downwards to Copy the formula in all the cells. • So, we have got the Absolute Deviations for all cells. • Again, we have to find the average or mean for all these Absolute Deviations.
• Therefore, write the following formula in E15 and Press ENTER.

`=AVERAGE(E5:E14)`

This function calculates the average of any given data range. • Thereby, we get the Mean Absolute Deviation which is 32.6. #### 1.2 Applying AVEDEV Function

This AVEDEV function is the easiest tool to calculate Mean Absolute Deviation.

• Write the following formula in C15 and Press ENTER.

`=AVEDEV(C5:C14)`

This function finds the Mean Absolute Deviation for a given data range. • Therefore, we get the Mean Absolute Deviation which is 32.6. Read More: How to Use AVEDEV Function in Excel (2 Suitable Examples)

### 2. Determining Median Absolute Deviation

Median Absolute Deviation is somewhat different from the Mean Absolute Deviation.

It determines the variance of data in respect to the median value.

We have to use the MEDIAN function to find the Median Absolute Deviation.

• First of all, modify your data table like the one shown in the below image. • Then, we have to find the Median of this dataset.
• So, write the following formula in D5 and Press ENTER.

`=MEDIAN(C5:C14)`

This function finds the Median of a given data range. • Thereby, we have got the Median value which is 530. Now, we have to find the Absolute Deviation in respect to the median value.

• So, write the following formula in E5 and Press ENTER.

`=ABS(C5-\$D\$5)`

The function returns the absolute (i.e. positive) value of a given number. So, we have got our first Absolute Deviation.

• Now, Hold and Drag the E5 cell downwards. • Doing so, we have got the Absolute Deviation for all the cells. Again, we have to find the Median of these Absolute deviations.

• So write the following formula in E15 and Press ENTER.

`=MEDIAN(E5:E14)`

This function returns the Median value of any given data range. • Therefore, we have got the Median Absolute Deviation which is 27.5. Read More: How to Calculate Variance and Standard Deviation in Excel

## Practice Section ## Conclusion

Thank you for making it this far. We have shown you different examples of calculating Absolute Deviation. We hope you find the content of this article useful. If there are further queries or suggestions, feel free to mention them in the comment section. You can visit our website ExcelDemy.com for more interesting topics about Excel.

## Related Articles Sourav Kundu

Hi, I am Sourav Kundu. I live in Adabor, Dhaka. I graduated in Naval Architecture and Marine Engineering from BUET. I am really excited to be a part of SOFTEKO family. I want to develop my research skill and find innovative solutions for the given problems.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  