How to Calculate the Median Absolute Deviation (MAD) in Excel: A Detailed Guide
Calculating the Median Absolute Deviation (MAD) in Excel can be a valuable tool for understanding the variability of a dataset. The MAD is a robust measure of statistical dispersion that is less sensitive to outliers than the standard deviation. In this guide, I’ll walk you through the steps to calculate the MAD in Excel, using both built-in functions and custom formulas.
Understanding the Median Absolute Deviation
The MAD is the median of the absolute deviations from the median of the data. In other words, it measures the average distance between each data point and the median of the dataset. This makes it a useful measure of variability, especially when outliers are present.
For example, consider a dataset of test scores: 85, 90, 92, 95, 100, 105, 110. The median of this dataset is 95. The absolute deviations from the median are: 10, 5, 3, 0, 5, 10, 15. The median of these absolute deviations is 5, which is the MAD for this dataset.
Using Excel’s Built-in Functions
Excel has a built-in function called ‘MEDIAN’ that can be used to calculate the median of a dataset. To calculate the MAD using this function, follow these steps:
- Enter your data into a column in Excel.
- Select a cell where you want to display the MAD.
- Use the following formula: =MEDIAN(MEDIAN(range)-range,0)
- Replace “range” with the actual range of your data.
This formula works by first finding the median of the data, then finding the median of the absolute deviations from that median, and finally subtracting the median of the data from the median of the absolute deviations. The result is the MAD.
Using Custom Formulas
While the built-in function is straightforward, you can also calculate the MAD using custom formulas. This can be useful if you want to understand the process behind the calculation or if you’re working with older versions of Excel that don’t have the MEDIAN function.
Here’s a step-by-step guide to calculating the MAD using custom formulas:
- Enter your data into a column in Excel.
- Select a cell where you want to display the MAD.
- Use the following formula: =MEDIAN(ABS(range-MEDIAN(range)))
- Replace “range” with the actual range of your data.
This formula works by first finding the median of the data, then subtracting that median from each data point to find the absolute deviations. The MEDIAN function is then used to find the median of these absolute deviations, which is the MAD.
Calculating the MAD for a Subset of Data
What if you only want to calculate the MAD for a subset of your data? For example, you might want to calculate the MAD for scores above a certain threshold. Excel makes this easy with the use of the FILTER function, which was introduced in Excel 365 and Excel 2019.
Here’s how to calculate the MAD for a subset of data:
- Enter your data into a column in Excel.
- Select a cell where you want to display the MAD.
- Use the following formula: =MEDIAN(ABS(FILTER(range, condition)))
- Replace “range” with the actual range of your data.
- Replace “condition” with the condition you want to filter by, such as “>90” for scores above 90.
This formula will calculate the MAD for the subset of data that meets the specified condition.
Example
Let’s say you have a dataset of test scores in column A, from A1 to A10. You want to calculate the MAD for scores above 90. Here’s how you would do it:
- Enter your data into column A.
- Select cell B1, where you want to display the MAD.
- Enter the following formula: =MEDIAN(ABS(FILTER(A1:A10, A1:A10>90)))
This formula will calculate the MAD for the scores in column A that are above 90.
