In this tutorial, we will learn how to calculate the standard deviation, the variance and the Z-score using Google Sheets. We talked about these quantities in one of our articles titled Data Analysis 101: Data Analysis Pitfalls To Watch For. Click here to read the article again.
For our tutorial, we will use a generated normal distribution of scores from Social Science Statistics. You can access the generator here.
There are several functions that you can use to calculate the standard deviation. The main ones are STDEV() and STDEVP(). To choose which of these functions to use, you need to know the answer to the following question:
Is it the dataset that you have just a slice or a sample of the entire population?
If your answer is yes, then use STDEV(). If your answer is no, as the dataset contains the entire population, then use STDEVP(). Both STDEV() and STDEVP() use the following syntax:
=STDEV(range)
where the range is the range of cells that contain the dataset. You can see the difference in their values below:
Finally, if the dataset contains text that should otherwise not belong there, both STDEV() and STDEVP() ignore them.
In a similar fashion, Google Sheets offers two main functions to calculate variance: VAR() and VARP(). Just like standard deviation, VAR() is used to find the variance of the sample while VARP() is used to find the variance of the population. Similarly, the syntax of VAR() and VARP() are the same as that of STDEV() and STDEVP():
=VAR(range)
where the range is the range of cells that contain the dataset. You can see the difference in their values below:
Similarly, VAR() and VARP() ignore cells containing text.
Finally, Google Sheets has a convenient formula for calculating the Z-score: Z.TEST(). This formula requires you to add the range of the distribution and the cell containing the specific number:
=Z.TEST(range, value)
If you plan to copy the formula by dragging the box to the lower-right corner of the blue highlighter in Google Sheets, make sure you append $ signs to keep it from changing. For example, our data is from A2 to A100. We want to calculate the Z-score on A3. The actual formula can then be written as follows:
=Z.TEST(A2:A100,A3)
The column now looks like this:
