The Z score indicates how many standard deviations a data point is located from the mean value of the dataset. You can use an Excel function or a simple formula to calculate the Z score.
➤ The Z score represents the number of standard deviations a data point is from the mean.
➤ Z score can be positive, negative, or equal to zero.
➤ STANDARDIZE function: =STANDARDIZE(x,mean,standard_dev)
➤ Arithmetic formula: z=(data point-mean)/standard deviation
In this article, we’ll learn about the Z score and two ways to calculate the Z score in Excel. We’ll use the STANDARDIZE function and arithmetic formula. Moreover, we’ll interpret the calculated Z scores.
What is Z Score in Excel?
The Z score (standard score) indicates how many standard deviations a data point lies from the mean. A Z score can be positive, negative, or equal to zero. The STANDARDIZE function in Excel returns the Z score. You can also use a simple formula to calculate the Z score.
where
x is a data point
μ is the mean
σ is the standard deviation
Let’s explore the methods to calculate the Z score in Excel.
Calculate Z Score with STANDARDIZE Function
In this dataset, we have the Math, Science, English, and History scores of a sample of students. We want to calculate the Z score of the total marks obtained in the four subjects.
Steps:
➤ Select the output cell (C13) and use the AVERAGE function to get the sample mean.
=AVERAGE(F2:F11)
➤ Calculate the sample standard deviation with the STDEV.S function.
=STDEV.S(F2:F11)
➤ In the G2 cell enter the formula to get the Z score.
=STANDARDIZE(F2,$C$13,$C$14)
➤ Drag the Fill Handle tool to copy the formula to the cells below.
Using Arithmetic Formula to Calculate Z Score
Let’s use the arithmetic formula to obtain the Z score for the same dataset.
Steps:
➤ Calculate the mean and standard deviation values like before. Use this formula to get the Z score.
=(F2-$C$13)/$C$14
➤ Use the Fill Handle tool to autofill the cells below.
Downloadable Resources
Get our practice workbook or necessary files that we have worked with to prepare this article.
Frequently Asked Questions
What is the formula for the Z score in Excel?
The formula to get the Z score is z=(data point-mean)/standard deviation.
How do I calculate the Z score without helper cells?
You can use this direct formula:
=STANDARDIZE(data point,AVERAGE(cell range),STDEV.S(cell range))
Insert the dollar signs to lock the cell ranges when copying the formula to other cells.
How to calculate the Z value from the table?
➤ Calculate the mean and standard deviation values of the dataset.
➤ Calculate the z score of a single data point, z=(data point-mean)/standard deviation.
➤ In the Z table, the left column shows the whole number while the top row represents the decimal numbers.
➤ The intersection of the row and column gives the proportion of data that lies below the calculated Z score.
Wrapping Up
In this quick tutorial, we’ve learned about the Z score and how to use the Excel functions and arithmetic formula to get the Z score. Feel free to download the practice file and let us know which method you like the most.
