ExcelInsider Team
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.
FAQ
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.
