Table of Contents
The Median Absolute Deviation (MAD) is a measure of dispersion that is often used in statistical analysis to determine the variability of a dataset. In Excel, the MAD can be calculated by first finding the median of the dataset, which is the middle value when the data is arranged in ascending order. Next, the absolute deviations of each data point from the median are calculated. These absolute deviations are then sorted in ascending order and the median of this new set of numbers is found. The MAD is then calculated by multiplying this median absolute deviation by a constant value of 1.4826. This process can be easily performed in Excel using built-in functions such as MEDIAN, ABS, and SORT. The resulting MAD value can provide valuable insights into the spread of the dataset and can be used for further analysis and decision making.
Calculate Median Absolute Deviation in Excel
The median absolute deviation is a way to measure the spread of values in a dataset.
The formula to calculate median absolute deviation, often abbreviated MAD, is as follows:
MAD = median(|xi – xm|)
where:
- xi: The ith value in the dataset
- xm: The median value in the dataset
The following step-by-step example shows how to calculate the median absolute deviation of a given dataset in Excel.
Step 1: Enter the Data
First, we’ll enter the values for a dataset:
Step 2: Calculate the Median
Next, we’ll calculate the median of the dataset:
The median turns out to be 16.
Step 3: Calculate the Absolute Difference Between Each Value & the Median
Next, we’ll calculate the absolute difference between each value and the median:
Click on cell B2. Then hover over the bottom right corner of the cell until a little cross (+) appears.
Step 4: Calculate the Median Absolute Deviation
Lastly, we will use the following formula to calculate the median absolute deviation of the dataset:
The median absolute deviation turns out to be 8.