The benefits of the z-score in statistics are manifold.
They help standardize raw scores of a normal distribution, help determine the probability of finding a given score within a standard normal distribution and also help compare two values that are from two different samples (that might have different means and standard deviations.
In this tutorial, we will show you how to compute the z-score in Excel.
Table of Contents
What is Z-score and How to Calculate It?
The z-score indicates how many standard deviations a given value is either below or above the mean.
It is also known as a Standard Score. You can find the z-score for any value in a given distribution if you know the overall mean and standard deviation of the distribution.
To calculate the z-score, we use the formula given below:Z = (x-µ) / σ
How to Calculate Z-score in Excel?
There is no direct formula to calculate the z-score in Excel, but its fairly easy to get.
As can be seen from the above formula, if you need to find the z-score for a given value in a set of data values, then you will need to first calculate the mean and standard deviation.
Consider a set of sample data shown below:
Let us see step-by-step how to compute the z-score for each of the data values in this dataset.
Note: The two functions in Excel that can be used to compute the standard deviation are:
You now have the z-scores corresponding to every value in your dataset!
How to Interpret the Z-score value
A z-score of 0 for a particular value indicates that the value in question is equal to the mean.
A positive z-score means that the value in question is more than the mean.
For example, if a value has a z-score of +1, it means the value is 1 standard deviation more than the mean, while a value with a z-score +2 is 2 standard deviations more than the mean.
Similarly, A negative z-score means that the value in question is less than the mean.
For example, if a value has a z-score of -1, it means the value is 1 standard deviation less than the mean, while a value with a z-score of -2 is 2 standard deviations less than the mean.
This also means that the higher the magnitude of the z-score (whether negative or positive), the further the value is from the mean.
So the z-score helps us clearly see how much a given value deviates from the mean.
For example, the 5th value in our dataset (cell A6) is 100, and we got a z-score for it of 2.622. You can thus say that the value 100 is 2.6 standard deviations below the mean, and this value is further away from the mean than the value (say) 28.
The z-score is especially helpful when youre trying to compare two different distributions that might have different means and variances.
For example, one cricket player might have scored 100 runs in a match, while another player might have scored 150 runs in another match.
Simply looking at the magnitude of the scores makes it easy to assume that player 2 is a better player than player 1.
However, they might have made those scores in different situations.
Maybe the competing team playing against player 2 was weaker, or maybe the weather was unfavorable the day player 1 made that score.
Comparing the z-scores of the two players could reveal that player 1 actually did better than player 2 in comparison to the scores of other players during their individual matches.
In this tutorial, we explained the concept of the z-score in statistics and how it is used and interpreted.
We also showed you how to use the AVERAGE and STDEV.P (or STDEV.S) functions to compute the z-score for a given set of values. We hope you found this helpful and easy to follow.
Other articles you may also like: