Z-score is an important part of statistics. In simple terms, Z-score refers to the number of values over or under a data point. It is used for several purposes, for example, statisticians use it to decide on individuals or populations for a given weight, and be aware of improvement or deteriorations over time. etc. Calculating the Z-score of given values manually is a time-consuming process. Therefore, many statisticians and academic institutions that conduct academic programs on statistics emphasize finding the values through MS Excel. However, not many students know how to find Z-score in Excel. If you are one among them, read this blog. Here, we have mentioned two methods of finding the values and also clarified the concept of Z-score.
What is Z Score?
Z score is utilized for statistical calculations. You may also know it by the name standard score. It is a statistical measurement that defines the relationship of the value to that of its mean. The value of the Z score is calculated in terms of standard deviations from the mean. If the value is 0, it indicates that the score of the data point is similar to that of the mean score.
To know how to find a z-score in Excel it is essential to learn its formula. You can measure the Z score by simplifying the following equation:
Z = (x-µ) / σ
Here, the provided arguments are as follows:
- Z = It refers to the Z score value.
- X = The value that will be standardized.
- µ = Mean of the provided data set values.
- σ = Standard deviation of the provided data set values.
Also read: What is a Variable in Math, its Types and Uses?
How to Calculate Z Score in Excel?
You can use 2 methods to find the precise values of the z-score in Excel. The first method is the long and traditional method while the second is a shortcut. The calculation of the Z-score in Excel using these methods is very simple and easy. Let us see how to calculate the Z score in Excel using examples.
-
Excel Z-Score – Method 1
Problem: 10 students of a class have scored the following grades.
- Daniel Smith: 465
- Mark Jonas: 477
- David Lewis: 566
- Paul Williams: 505
- Nick Holmes: 596
- Patrick Rodriguez: 459
- Samuel Lopez: 524
- Monica Samson: 460
- Adrian Brave: 460
- Ronal Chris: 490
Find their Z-score, mean, and standard deviation in an Excel sheet.
Solution:
To know how to find the Z-score in Excel, you must first determine its mean and standard deviation
v Calculation of the Mean (or Average) of the given data
To find the average of the scores achieved by these students, adhere to the below steps:
Step 1: Click on the Formulas tab. Go to the function library section and choose the option for More Functions.
Step 2: From the drop-down list choose the Statistical functions category. It will then open a variety of functions. Click on the AVERAGE function.
Step 3: It will unlock a dialog box for Function Arguments. Put in the range of cells on which you have enlisted the scores of the students, for example, B3:B12. Place it under field Number 1 and press on OK.
You will get the Average or Mean value.
v Calculation of Standard Deviation
To find the standard deviation of the data, stick to the steps mentioned below:
Step 1: Hover over the Formulas tab. Go to the Function Library section. Press the More Functions option.
Step 2: From the drop-down menu choose the Statistical Function section and click on it. After that, choose STDEVPA from the list of options.
Step 3: It will unlock a dialog box filled with Function Arguments. Below field Value 1, enter the range of cells where you have entered the students’ scores. Make sure it tallies the range you mentioned for calculating the mean of the scores, for example, if you have selected the cell ranges B3:B12 for calculating the mean, select the same for measuring the standard deviation as well. Finally, click on OK.
It will give you the value of the standard deviation.
Read more: Best Math Books To Augment Your Skills
v Calculation of Z-score
Now that you have the values of standard deviation and mean, you can easily compute the Z-score in Excel. Here are the steps to follow.
- Hover over the FORMULAS tab. Go to the Function Library section and choose the More Functions option.
- From the drop-down menu of functions select the statistical functions. You will find a list of functions. Select the STANDARDIZE function from the list and press on it.
- Then it will unlock a Function Arguments dialog box. You will find a variety of fields. Go to the field X and put the Cell value B4 under it.
- In the 2nd field put the Mean value. Put the cell value B14 under it. Note: If you have put the scores in the range B3:B12, then you must mention the mean score under Cell B14.
- In the 3rd field put the value of Standard Deviation under Cell B15. Standard deviation is usually mentioned as Standard dev in the dialog box. Finally, click on OK.
- You will get the result for one of the scores of the students.
- Repeat the steps for all other values. It will pop up the Z-score for all the scores on the Excel sheet.
- If you examine the data, you will notice that the highest value of the Z score is 2.082778. It belongs to that of Nick Brown. He has achieved the highest score in the exam.
- The smallest value of the Z score value is -0.98521. It belongs to that of Adrian Steve. He has attained the lowest score in the exam.
- Note:
- Z scores can have values in positive and negative values.
- Students who have attained scores more than the mean value get the value of Z scores in positive.
- Students who have attained scores less than the mean value get the value of Z scores in negative.
- If the value of Z score is zero, the score of the student is the same as the mean value.
-
Excel Z Score – Method 2
Problem: For the following values, find the mean, standard deviation, and Z-score using Excel
- 300
- 123
- 4350
- 175
- 956
- 278
- 5436
- 3654
- 900
- 736
Also read more: What Is A Term In Math, And How To Solve A Mathematical Term?
Solution:
How to find the Z-score of these values? The best way is to determine the mean and standard deviation first and then calculate their Z-scores.
- Calculation of the mean of the values:
- First, get the mean of the data sets. For that, use the AVERAGE formula. If you place the values in the cell range A3:A12, type “Mean” in the A14. After that mention the formula “=AVERAGE(A3:A12)” in the cell B14.
- Finally, press Enter.
- You will get the mean of the values.
- Calculate the standard deviation of values:
- To calculate the standard deviation use the STDEVPA function for the given data values. It is quite similar to the steps followed for finding the value of the mean. Here, as well, type the formula “=STDEVPA (A3:A12)”.
- After that, press enter.
- You will get the value of the standard deviation.
· Calculation of the Z-score of the values
- To find the measure of the Z score values in Excel use the STANDARDIZE function for the mentioned data values. For cells A3:A12, the formula for standard deviation will be =STANDARDIZE(A3,$B$12,$B$15). Enter it on the B3 cell and press enter.
- The result will appear.
- After that, drag this formula for the rest of the values. The Final result will be:
- -0.6925926 for the value of 300
- -0.7847093 for the value 123
- 4151622 for the value of 4350
- -0.7576488 for the value of 175
- -0.6634483 for the value of 356
- -.7040421 for the value of 278
- 9803527 for the value 5436
- 0529406 for the value 3654
- -0.38503326 for the value of 900
- -0.4656837 for the value 736
You can observe that the positive value of Z scores is higher than the mean value. In contrast, the negative value of Z scores is lower than the mean value.
Also read: How to Solve Math Assignment Problems Faster?
Conclusion
The discussion above highlights how to find Z-score in Excel. There are 2 ways you can use to measure the values. The first method involves opening various tabs on the upper ribbon and to get the result. However, the shortcut steps are all about using the formulas. Use the method you find helpful.
