Calculating Z-Score in Google Sheets is fairly easy once you learn the proper formula to do this.
In this tutorial, I will show you how to calculate Z-Score in Google Sheets.
Table of Contents
What is Z-Score?
A z-score (also called a standard score), is a number that is used in statistics that will tell you the distance from the mean of a data point, measured in standard deviations.
Essentially, the z-score is how many standard deviations above or below the mean a data point is.
If the z-score is positive, the data is above the mean, if it is negative it is below the mean.
The formula to calculate z-score is raw score minus the mean, divided by the standard deviation:
Z-Score formula: (Score – Mean)/Standard Deviation
Once you learn this formula you will see that it is fairly easy to do this inside your spreadsheet. I will show you how to calculate it in the next section.
Calculating Z-Score in Google Sheets
I will be calculating the z-score for this example data:
In order to calculate the z-score, I also need to calculate the average (mean) and standard deviation.
Here are the steps for making all of these calculations.
1. First we will calculate the average by using the AVERAGE function. In the function, simply put in the data range that contains your values. In my example it is =AVERAGE(A2:A12)
2. Next we need to find the standard deviation. If you have the entire population of scores you can use the STDEV.P function, if you have a sample of scores you can use the STDEV.S function. Put in your range that contains your values into this function. In my example, the formula is =STDEV.P(A2:A12)
3. Once we have the average and standard deviation, we can calculate the z-score. Remember from above, the formula is =(Score – Mean)/Standard Deviation. So in my example, this is =(B2–C2)/D2. In my example, the z-score is .399
Closing Thoughts
Remember, the z-score shows you how many standard deviations above or below the mean a data point is.
In my example, the z-score is .399 which means the data point that I calculated the z-score for is .399 standard deviations above the mean.
Be sure to watch the video if you are still having trouble with this!
More Google Sheets Tutorials:
How to Calculate p-Value
How to Use the Z.TEST function
How to Calculate Confidence Interval