Step 3) Subtract the mean for this dataset from each value of the dataset. We will call this value x.
Click to copy
Note that I have used an absolute reference for the mean (Cell G2). I don’t want Excel to change the cell reference G2 (the mean) when I drag the formula down across the column. Take care of that.
Kasper Langmann, co-founder of Spreadsheeto
Step 4) The final step is to calculate the Z-score. Divide X by the Standard Deviation of this dataset through the following formula.
Click to copy
There you have your z-score values for this dataset calculated. It was all about a few easy steps 🤘
You’d see some z-scores as positive values and other as negative values.
This tells if the weight of a person is greater than the mean of the dataset (a positive z-score) or smaller than it (a negative z-score).
We will cover more details on how to interpret z-scores in the later sections.
Use the STANDARDIZE function
The second and relatively easier method to calculate z-scores in Excel is to use the STANDARDIZE function of Excel.
It is built into Excel and helps you calculate z-scores for any given dataset in a snap ⌛
Step 1) Begin writing the STANDARDIZE function as below.
Click to copy
It has 3 arguments. By x, it means the values from the dataset. The other two are the mean and standard deviation of the dataset.
Step 2) Refer to the first value from the dataset as the first argument.
Click to copy
Step 3) For the mean argument, nest in the AVERAGE function as follows.
Click to copy
Step 4) For the standard_dev argument, nest in the STDEV.S function as follows.
Click to copy
Make sure to use absolute references for the mean and the standard deviation argument so that the formula can be dragged down easily ⏬
Step 5) The function is ready. Give it the go-ahead by pressing Enter.
Step 6) Drag this formula down the list of weights.
The STANDARDIZE function calculates the z-score for each value of the dataset.
And the results are the same as that of our manual calculation. Both the methods for z-score calculation work absolutely fine – choose the one that you find better.
How to interpret Z-score
A z-score tells where your data stands in a data distribution. For example, if the Z-score for a data point is 1.5, this tells that the data point is 1.5 times away from the mean on the higher side 📝
The smaller or bigger the Z-score of a data point, the farther away it is from the mean of that dataset. It is not possible to objectively say if it is good or bad to have a smaller or bigger Z-score.
It will depend on what the data represents and how you want it to behave. To say if you’re an investor who is evaluating the return on his investments. A higher Z-score means your investment is performing better and offering higher returns than the average return on investments.
Hence, a bigger Z-score is not something bad in the said situation. It only tells how far or close each value of a data lies to its mean 🎯
In a standard normal distribution of data:
- 65% of the data values will lie within -1 to 1 z-score.
- 95% of the data values will lie between -2 to 2 z-score
- And around 99% will lie between -3 to 3 z-score
Z-scores smaller or greater than -3 / +3 might require investigation (can call them outliers)
A Z-score of 0 means the value is the same as the mean of the dataset.
Kasper Langmann, co-founder of Spreadsheeto
Conclusion
Z-score is a very useful statistical measure that quickly tells you how far each value of your datum lies from its mean.
What makes it useful is the fact that it expresses this distance in terms of the data’s standard deviation. So, if a value lies absolutely one standard deviation away from the mean (at a standard spread), the Z-score for it would be a precise -1 or +1.
To learn more interesting statistical concepts, read out my following Excel tutorials, I’m sure you’d love them equally.
- How to Calculate P Value in Excel: Step-by-Step Guide (2024)
- How to Create a Box and Whisker Plot in Excel (2024 Guide)
- How to do Linear Regression in Excel: Full Guide (2024)
