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.
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.
This function calculates the average of any given data range. The function returns the absolute (i.e. positive) value of a given number. This function calculates the average of any given data range. This AVEDEV function is the easiest tool to calculate Mean Absolute Deviation. This function finds the Mean Absolute Deviation for a given data range. Read More: How to Use AVEDEV Function in Excel (2 Suitable Examples) 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. This function finds the Median of a given data range. Now, we have to find the Absolute Deviation in respect to the median value. The function returns the absolute (i.e. positive) value of a given number. So, we have got our first Absolute Deviation. Again, we have to find the Median of these Absolute deviations. This function returns the Median value of any given data range. Read More: How to Calculate Variance and Standard Deviation in Excel You can download the practice workbook where you can practice yourself. 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.=AVERAGE(C5:C14)
=ABS(C5-$D$5)
=AVERAGE(E5:E14)
1.2 Applying AVEDEV Function
=AVEDEV(C5:C14)
2. Determining Median Absolute Deviation
=MEDIAN(C5:C14)
=ABS(C5-$D$5)
=MEDIAN(E5:E14)
Practice Section
Conclusion
Related Articles
