Table of Contents
Z-scores, also known as standard scores, are a statistical measure used to determine how far a data point is from the mean in terms of standard deviations. In order to calculate Z-scores in Google Sheets, you will need to use the formula “=STANDARDIZE(x,mean,standard_dev)” where “x” represents the data point, “mean” is the mean of the data set, and “standard_dev” is the standard deviation. This formula will return the Z-score for the given data point. You can then apply this formula to the entire data set to calculate the Z-scores for all data points. This can be useful in analyzing and comparing data sets for things like standardized tests or performance evaluations.
Calculate Z-Scores in Google Sheets
In statistics, a z-score tells us how many standard deviations away a value is from the mean. We use the following formula to calculate a z-score:
z = (X – μ) / σ
where:
- X is a single raw data value
- μ is the mean of the dataset
- σ is the standard deviation of the dataset
This tutorial explains how to calculate z-scores for raw data values in Google Sheets.
Example: Z-Scores in Google Sheets
Suppose we have the following dataset and we would like to find the z-score for every raw data value:
We can perform the following steps to do so.
Step 1: Find the mean and standard deviation of the dataset.
First, we need to find the mean and the standard deviation of the dataset. The following formulas show how to do so:
The mean turns out to be 14.375 and the standard deviation turns out to be 4.998.
Step 2: Find the z-score for the first raw data value.
Next, we’ll find the z-score for the first raw data value by typing the following formula in cell B2:
=(A2–$E$2)/$E$3
Once we’ve calculated the first z-score, we can highlight the rest of column B starting with cell B2 and press Ctrl+D to copy the formula in cell B2 to each of the cells below it:
Now we have found the z-score for every raw data value.
How to Interpret Z-Scores
A z-score simply tells us how many standard deviations away a value is from the mean.
In our example, we found that the mean was 14.375 and the standard deviation was 4.998.
So, the first value in our dataset was 7, which had a z-score of (7-14.375) / 4.998 = -1.47546. This means that the value “7” is -1.47545 standard deviations below the mean.
The next value in our data, 12, had a z-score of (12-14.375) / 4.998 = -0.47515. This means that the value “12” is -0.47515 standard deviations below the mean.
The further away a value is from the mean, the higher the absolute value of the z-score will be for that value. For example, the value 7 is further away from the mean (14.375) compared to 12, which explains why 7 had a z-score with a larger absolute value.
Additional Resources
How to Calculate Z-Scores in Excel
How to Calculate Z-Scores in R
How to Calculate Z-Scores on a TI-84 Calculator