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

**Table of Contents**hide

## Download Practice Workbook

You can download and practise this workbook.

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

You can download the practice workbook where you can practice yourself.

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