Z-score, or the standard score, is a mathematical function that allows you to have an idea of the distance between a value and the mean (average) of the data set. The Z-test in Google Sheets is a beneficial function for individuals who tend to compare a lot of data in their spreadSheets or those who work with mathematical equations.
This article explains the Z score sheet function, how to calculate Z score in Google Sheets, and how you can interpret the values you find using this function.
Table of Contents
What Is a Z-score?
The Z-score, also known as the standard score, is a number used in mathematics to calculate the distance from the mean of the data point and is measured using standard deviations.
To explain in easier-to-follow words, Z-score defines how beneath the mean the data point is or how many standard deviations above it lies.
If the Z-score is negative, it means it’s below the mean, and if it’s positive, the data set is above the mean. To find the Z-score, subtract the mean from the score and divide it by its standard deviation. The formula for this is:
Z = (X – μ) / σ
Here:
X –Â is used to represent the single data value.
μ – is used to represent the mean of the data.
σ – is used to represent the standard deviation of the data.
How to Calculate Z-score in Google Sheets
Here are the steps you need to follow to find the Z score in Google Sheets:
Step 1: To calculate the Z-score in Google Sheets, you first have to find the standard deviation and the mean of the data in your spreadsheet. To do this, we use the STDEVP and the AVERAGE formula, respectively.
Note:Â To use each of these formulas simply:
- Â Click an empty cell, type =Â and the function name
- Highlight the cells with your data
- Press Enter
Step 2:Â To find the Z-score for the first value in your spreadsheet, we will be using the formula we discussed above. Here is the syntax for the formula:
Z-score = (Value - Mean) / Standard Deviation
In this particular case, to find the Z score for a single value we will use the cell addresses as references in the formula. So, we type:
 =(A2-E3)/E2
Step 3: To execute the formula without having to type it in every cell individually, click on the fill handle (the blue dot in the bottom right part of the cell) and drag it down to cover the cells. But, you may notice that you start getting 0 values.
You’ll need to add absolute references to your formula to fix this.
Ensure to add the dollar symbol ($) in the parts of the formula to show that you want to use absolute references.
Absolute references in Google Sheets formulas indicate cells, columns, or rows that shouldn’t be changed when auto-filling a formula. In our example, we’ll be using the same cell for standard deviation and the average. So we put a $ in front of the column and row reference for those cells. So, our formula should look like this:
=(A2-$E$3)/$E$2
Interpreting Z-scores
The Z score Google Sheets calculation is performed to check how many standard deviations apart a value is from the mean value of the dataset.
In our example data, the standard deviation was 14.58541652, while the mean was 16.125. The first value in the example data was 3. The Z-score was calculated by this process:
=(3-16.125) / 14.58541652
=-0.8998714561
The negative symbol tells us that the value is below the mean, while the value tells us the number of standard deviations the value is away from the mean.
To further clarify this concept, let’s look at the highest value in the dataset, 43. The Z-score was calculated using the following process:
=(43-16.125) / 14.58541652
=1.842593934
As the value is positive, we know the value is above the mean value of the data. Similarly, the data value defines the number of standard deviations the value is away from the mean.
Z Score Google Sheets FAQ
How to Find Z-score on Google Sheets?
The formula for calculating the Z-score in Google Sheets is
= (Value - Mean) / Standard Deviation
Before you execute this formula, you need to calculate the mean and the standard deviation of the values in your data set.
How Do You Use NORMDIST in Google Sheets?
The NORMDIST function calculates the normal distribution for a specified mean and standard deviation. Here is the syntax for the formula:
=NORMDIST(value, mean, standard_deviation, cumulative)
- x – The input
- mean – The mean (Average)
- standard_deviation
- cumulative – A TRUE or FALSE value to apply cumulative frequency or not
What Is the Z-Score for a 90 Confidence Interval?
To be a 90% confidence level, a Z-score should be < -1.65 or > +1.65.
What Is Z-score?
Z-score is a mathematical function used to describe the relationship between a data value and the mean of the dataset. You measure it in terms of the standard deviations from the mean.
The value of the Z-score can either be positive or negative. If the Z-score is negative, it means it’s below the mean, and if it’s positive, the data set is above the mean.
Closing Thoughts
Although there is no exact Z-score function in Google Sheets, completing these can be a highly beneficial method to calculate other statistical points, especially for people who do a lot of mathematical calculations in their spreadsheets.
We hope this article helped you better understand what the Z Score Google Sheets function is, how you can use it, and ways to interpret the output given by the function.
Here are a few other articles that will help you out:
