Table of Contents
Z-scores, also known as standard scores, are a statistical measurement used to compare data points to a population’s mean and standard deviation. In Excel, you can easily calculate Z-scores using the built-in function, NORM.S.DIST. This function takes the data point, mean, and standard deviation as inputs and returns the Z-score. Simply enter the function into a cell, input the necessary values, and press enter to calculate the Z-score. This can be repeated for multiple data points to quickly and accurately compare them to the population’s distribution. Z-scores in Excel are a useful tool for analyzing and understanding data in a standardized way.
Calculate Z-Scores in Excel
In statistics, a z-score tells us how many standard deviations away a value is from . We use the following formula to calculate a z-score:
z = (X – μ) / σ
where:
- X is a single raw data value
- μ is the mean of the dataset
- σ is the standard deviation of the dataset
This tutorial explains how you can calculate z-scores for raw data values in Excel.
How to Calculate Z-Scores in Excel
Suppose we have the following dataset and we would like to find the z-score for every raw data value:
We can perform the following steps to do so.
Step 1: Find the mean and standard deviation of the dataset.
First, we need to find the mean and the standard deviation of the dataset. The following formulas show how to do so:
The mean turns out to be 14.375 and the standard deviation turns out to be 4.998.
Step 2: Find the z-score for the first raw data value.
Next, we’ll find the z-score for the first raw data value using the formula z = (X – μ) / σ.
Cell C2 shows the formula we used to calculate the z-value in cell B2.
Now that we found the z-score for the first value in the dataset, we can simply copy the formula we used in cell B2 to the rest of the data values. We can do this by highlighting the entire z-score column, starting with the first z-score we already calculated:
Next, press Ctrl+D. This copies the formula in the first cell to all of the cells below it.
Now, we have found the z-score for every raw data value.
How to Interpret Z-Scores in Excel
Recall that a z-score simply tells us how many standard deviations away a value is from the mean.
A z-score can be positive, negative, or equal to zero.
A positive z-score indicates that a particular value is greater than the mean, a negative z-score indicates that a particular value is less than the mean, and a z-score of zero indicates that a particular value is equal to the mean.
In our example, we found that the mean was 14.375 and the standard deviation was 4.998.
So, the first value in our dataset was 7, which had a z-score of (7-14.375) / 4.998 = -1.47546. This means that the value “7” is -1.47545 standard deviations below the mean.
The next value in our data, 12, had a z-score of (12-14.375) / 4.998 = -0.47515. This means that the value “12” is -0.47515 standard deviations below the mean.
The further away a value is from the mean, the higher the absolute value of the z-score will be for that value.
For example, the value 7 is further away from the mean (14.375) compared to 12, which explains why 7 had a z-score with a larger absolute value.
Additional Resources
The following articles provide further information on how to work with z-scores in Excel: