How to Calculate Z Score in Excel
The z-score, also known as the standard score, is a measurement used in statistics. It is the measurement of the number of standard deviations that a specific number is above or below a mean. The formula to calculating the z-score is:
z = (x – μ) / σ
where:
- z is the z-score,
- x is the value to be standardized,
- μ is the mean of the given set of data,
- σ is the standard deviation of the given set of data.
Suppose you are a student and there are a total of 10 students in your class, including you. After the final exam result is published, you will want to know how well you performed compared to the average student’s mean score.
You can find this easily using z-score.
Calculate z-score
Open Excel and save your file as zscore.xlsx. Type “Name” in A1, “Marks” in B1, “z-score” in C1, “Mean” in F1 and “Standard Deviation” in F2. You should not enter the double quotes when you type in the data.
You can format these cells and make them bold.
In cells A2 to A11, type ten different names, and in cells B2 to B11, type ten different marks.
Now, your screen will look like this, though the entries are different.
Click on cell G1 and go to Formulas (main menu) –> More Functions (in the Function Library group) –> Statistical and select the AVERAGE function.
You will get a screen like this:
In the text box Number1, enter B2:B11 and click OK.
Click on cell G2 and go to Formulas (main menu) –> More Functions (in the Function Library group) –> Statistical and select the STDEVPA function.
You will get a screen like this:
In the text box Value1, enter B2:B11.
Click on cell C2 and go to Formulas (main menu) –> More Functions (in the Function Library group) –> Statistical and select the STANDARDIZE function.
You will get a window like this:
Enter “B2” in the X text box, enter “$G$1” in the Mean text box, and “$G$2” in the Standard_dev text box. Click OK and you will get a value in the cell C2. Copy the formula in cell C2 and paste it into cells C3–C11.
Instead of using the STANDARDIZE function, you can directly enter the formula in the address bar:
In this case, we are using the formula to calculate the z-score directly instead of using the built-in STANDARDIZE function.
If you analyze the data, you will find that the highest z-score value is 1.600279 which is the z-score of Paul Lopez, who scored the highest marks of 592. Sarah Chris has got the lowest z-score and, obviously, the lowest score in the exam.
You can find both positive and negative z-scores. Students who got scores higher than the mean (value in cell G1) get positive z-scores and those who scored less than the mean get negative z-scores. If the z-score of a student is zero, it means that his or her score is the same as the mean value. We can test this easily. Adjust the scores in such a way that one of the students’ scores is the same as the average.
Here, you could find that the z-score of Sarah Chris is 0 because her score is the same as the average score. Students who scored more than 500 have a positive z-score and those who scored less than 500 have a negative z-score.
Leave a Reply