In statistics, a modified z-score is calculated as:
Modified z-score = 0.6745(xi – x̃) / MAD
where:
- xi: A single data value
- x̃: The median of the dataset
- MAD: The median absolute deviation of the dataset
A modified z-score is more robust than an ordinary z-score because it uses the median in its formula as opposed to the mean, which is known to be influenced by outliers.
Iglewicz and Hoaglin recommend that values with modified z-scores less than -3.5 or greater than 3.5 be labeled as potential outliers.
The following step-by-step example shows how to calculate modified z-scores for a given dataset in Excel.
Step 1: Create the Data
First, we’ll create the following dataset that contains 16 values:
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:
The absolute difference between the first data value and the median turns out to be 16.
Next, click on cell B2. Then hover over the bottom right corner of the cell until a little cross (+) appears.
Double click the cross to copy and paste this formula to all remaining cells in the column:
Step 4: Calculate the Median Absolute Deviation
Next, we will use the following formula to calculate the median absolute deviation of the dataset:
The median absolute deviation turns out to be 8.
Step 5: Find the Modified Z-Score for Each Data Value
Lastly, we can calculate the modified z-score for each data value using the following formula:
Modified z-score = 0.6745(xi – x̃) / MAD
For example, the modified z-score for the first data value is calculated as:
Next, click on cell C2. Then hover over the bottom right corner of the cell until a little cross (+) appears.
Double click the cross to copy and paste this formula to all remaining cells in the column:
We can see that no value in the dataset has a modified z-score less than -3.5 or greater than 3.5, thus we wouldn’t label any value in this dataset as a potential outlier.
How to Handle Outliers
If an outlier is present in your dataset, you have a few options:
- Make sure the outlier is not the result of a data entry error. Sometimes an individual simply enters the wrong data value when recording data. If an outlier is present, first verify that the value was entered correctly and that it wasn’t an error.
- Assign a new value to the outlier. If the outlier turns out to be a result of a data entry error, you may decide to assign a new value to it such as the mean or the median of the dataset.
- Remove the outlier. If the value is a true outlier, you may choose to remove it if it will have a significant impact on your overall analysis. Just make sure to mention in your final report or analysis that you removed an outlier.
Additional Resources
How to Calculate Z-Scores in Excel
How to Calculate the Interquartile Range (IQR) in Excel
How to Calculate the Midrange in Excel