The Z-score of a distribution is a statistical value that represents how many standard deviations away from the mean a given data point is.
Formula to calculate z-score in Google SheetsZ = (Datapoint - Mean)StdevDatapoint – The data for which to calculate the z-score.Mean – The average value of the dataset.Stdev – The standard deviation of the dataset.
Sample Usage=(B3-D3)/E3
//This will return the z-score of the value in cell B3. D3 is the mean of the dataset; E3 is the standard deviation.
Copy the spreadsheet template here.
The Z-score is an important tool employed in several analyses. It is used to represent the relationship between a value in a data set and the mean. We can obtain this Z-score by using the Z-formula in Google Sheets to examine a given data set.
What is a Z-score?
The Z-score of a distribution or sample is a statistical value that represents how many standard deviations away from the mean a given data point is.
For instance, if a data point is located at point 3 on the X-axis of a normal distribution graph with the mean at zero, that point is said to have a Z-score of 3. Which means it is 3 standard deviations from the mean.
Significance of the Z-score formula
Introduced by Edward Altman in the 1960s, the Z-formula is widely used widely in the business realm. Traders and statisticians can determine normal and abnormal values in a given data sample. Given that most points should tend towards a central value, one can make informed decisions based on the behaviour of data points in a sample relative to the mean.
Knowledge of this relationship can be vital for analysis in areas such as finance where traders can apply it in calculating market volatility and a company can ascertain its closeness to bankruptcy. With appropriate historical data, analysts can create samples and distributions through which they examine data points of interest relative to the central value.
Such data points can either be on the negative side of the mean which means below the mean value or on the positive side which means above the mean value.
We can conveniently calculate the Z-score of a distribution in Google Sheets. This tutorial aims to show you how to do this using simple and easy-to-follow examples. But before the examples let’s briefly look at the underlying concepts of the Z-score formula.
Syntax
Formula to calculate z-score in Google Sheets:
Z = (Datapoint - Mean)/StdevWhere the parameters of this function are:
- The mean of the distribution is the average value of a given data set. It represents the central value that other values in the set tend towards or are spread about.
- Stdev (The standard deviation) is a representation of the variability i.e variance of the points in the dataset. The standard deviation considers how spread out each point is from the mean. Mathematically, it is given as the square root of the variance.
Therefore, the Z-score is an expression of the standard deviation and tells us how much of its value a point is below or above the mean.
Note: In most large datasets, 99% of the data points will be between -3 and 3 standard deviations away from the mean. This is known as the empirical rule in statistics. And it is important for calculations of the normal distribution of samples.
How to calculate Z-score in Google Sheets
To calculate Z-score in Google Sheets we simply have to obtain all the values needed for the Z-score formula. In the following steps, we will obtain the mean and standard deviation of the data set below.
The image above represents profits a Startup made in the past year. We will analyze this data using the Z-score formula to understand each point’s performance relative to the mean.
Step 1: Calculate the mean for the z-score formula
We can obtain the mean value of the dataset using the AVERAGE formula in Google Sheets.
Syntax
= AVERAGE(value1, [value2, ...])where value1 refers to the data range for which the formula returns a mean.
- Enter the AVERAGE formula into a cell
- Select B3:B13 as data range
=AVERAGE(B3:B13)
- Press Enter
The mean value for our data is 92.73. Next, we’ll calculate the standard deviation.
Step 2: Calculate the standard deviation for the Z-score formula
Google Sheets has simple formulas to calculate the standard deviation of a sample or an entire population — STDEV.P or STDEV.S. We will calculate for the entire population.
Syntax
=STDEV.P(value1, [value2, ...])value1 – The first value/range of the population.
value2 – optional values/ranges to include in the population.
- Enter the formula into an empty cell.
- Select B3:B13 as the data range, the formula should be
=STDEV.P(B3:B13)- Press Enter
The standard deviation for our dataset is 30.53490077.
Step 3: Calculate Z-score in Google Sheets
Now that we have obtained the needed values, we can obtain the Z-scores for the corresponding data points using the Z-score formula. Let’s see how.
Recall that the Z-formula is given as:
Z-score = (Datapoint - Mean)/Stdev- In an empty cell enter the formula below
=(B3-D3)/E3Here, we chose cell D3 as the mean and cell E3 as the standard deviation.
- Press Enter to calculate the first Z-score.
Tip: Now that we have gotten the first Z-score it is very easy to calculate the remaining cells using auto-fill options in Google Sheets. To do this we have to first make the mean and standard deviation values absolute cell references.
=(B3-$D$3)/$E$3We can now calculate the values for other cells by dragging down the blue dot on the right corner of cell G3.
So let’s do that to get other Z-scores
Conclusion
Z-scores can help analyze our data about a central value, as well as enable us to know when they are abnormalities. To calculate Z-score in Google Sheets, find the mean and standard deviation of the dataset. Then use the Z-formula to find individual scores.
See also
You might also like other similar articles in our blog:
https://blog.tryamigo.com/z-test-function-in-google-sheets/
https://blog.tryamigo.com/using-binomdist-function-in-google-sheets/