In this tutorial you learn How to Calculate Z scores in Google Sheets
Calculate Z scores in Google Sheets
To calculate the Z scores in Google Sheets you can use the formula which will compound a Z score “=(DataValue-Mean)/StDev“.
How to Calculate Z scores in Google Sheets : Step by Step Guide
Here are the steps to Calculate Z scores in Google Sheets
Step 1 : Open Google Sheets and prepare your dataset
In this example we will use the below dataset with marks scored out of 10
Step 2 : Calculate the Mean and Standard deviation
First we will calcuate the Standard deviation and Mean using the STDEVP and the AVERAGE formula
To calculate the standard deviation ,Click on cell D2 under Heading STDDEV and type the below formula in fx function area (adjust the formula as per your requirement.)
=STDEVP(A2:A14)
To calculate the mean Click on the Cell C2 under Heading Mean and type the below formula (adjust the formula as per your dataset ) and press enter
=AVERAGE(A2:A14)
Now we have calculated both the standard deviation and Mean .Value for Standard deviation is 2.058321255 and Mean is 5.384615385
Step 3 : Calculate the Z-Score
To calculate the Z-Score for the first value in our data set we will use the below formula
Z-score = (Data Value - Mean) / Standard Deviation
Based on above formula we will type the following syntax in cell B2 and press enter
=(A2–$C$2)/$D$2
Confirm the suggested auto-fill to calculate the Z-score for remaining values in your dataset.
Z-score has been calculated for all the values in your dataset.
- You will notice a dollar Sign $ being used in the formula which are used as absolute marked in the formula .
- They direct the function to always check in a particular cell for value even if somone copies the formula to other cell.
In our formula =(A2–$C$2)/$D$2 it will always look for the mean value in Cell C2 and Standard deviation on cell D2.
How to interpret the Calculated Z-Score result
The Z score calculation is used to determine how many standard deviations there are between a value and the dataset’s mean value.
In our example, the Calculated values for standard deviation and mean were :
- Standard deviation : 2.058321255
- Mean : 5.384615385
The calculated Z score for the first value in our data set was
(3-5.384615385) / 2.058321255 = -1.158524394
This indicates that the data value “3” is -1.158524394 standard deviations below the mean.
While the value indicates how many standard deviations the value is from the mean, the negative symbol indicates that the value is below the mean.
The calculated Z score for the second value in our data set was
(6-5.384615385) / 2.058321255 = 0.2989740371
This indicates that the data value “6” is 0.2989740371 standard deviations below the mean.
How to Calculate Z scores in Google Sheets (Conclusion)
In the above tutorial we have shown you a step by step guide to calculate Z score in Google Sheets.
We hops this tutorial on How to Calculate Z scores in Google Sheets was useful.
Related articles :
How to Insert Spin Button in Google Sheets ( Easy Guide )
How to Freeze Rows in Google Sheets
How to Calculate Square Root and Cube Root in Google Sheets(Quick & Easy Guide)
How to use SUMSQ Function in Google Sheets(Quick & Easy Guide )
