Z Score in Excel: Z-score is one of the essential statistical measurements that help to compare observations. That is why several researchers and statisticians want to know the exact method of how to find Z score in excel.
Several applications of z-score, such as in research- are used for comparing various observations taken from different populations using the mean value and standard deviation. Apart from this, a z-score is the measurement of various observations used to determine market volatility.
So, let’s learn various key points related to the z-score and how to calculate z score in excel. And move to the details without wasting more time.
What is actually a z-score?
Table of Contents
A z-score is the method to compare the two or multiple data sets’ values. Or it can be defined as the standard deviation that is away from the average of a data point. A z-score can be calculated using the function:
| =[DataPoint – MEAN (DataSet)] / STDEV (DataSet) |
Let’s take an example that will help you to clear the concept of z-score. Suppose you need to compare the result of two students who are studying Algebra taught by two different teachers. From both students, student one scored 95% and second 87% in their respective exams.
If you see both scores, then it is obvious that a 95% score is more impressive. But, there is also a possibility that the second teacher gave a tough exam. Here, you can measure the z-score of the score of each student that depends on the mean and standard deviation score of the class.
Comparing the z-score of the students might show that the student who scored 87% performed better than the other students who have scored 98%.
Now the question is: Why do z-score important?
It is always beneficial to normalize the raw score (value) of the normal distribution by changing it into the z-score as:
- Z-score enables researchers to measure the score’s probability that happens in the standard normal distribution;
- It also allows users to compare multiple (two) scores taken from the different samples (with different standard deviation and mean).
What is the practical use of the z-score?
It has been seen that WHO/NCHS reference values demand an accurate method to assess the infants’ and young children’s nutrition status. The expert team of WHO recommended the new reference concern about the weight and height of children and infants.
The three methods used for referencing are “percentage of the median,” “z score,” and “percentiles.”
- Percentage of median: It is the ratio of a child’s weight with the child’s median weight of the same height that lies in the reference data.
- Z-scores: It shows how far a child’s weight is from the median weight in the reference data.
- Percentiles: It is the position of an individual’s weight (with a particular height) in the reference values.
Practically, the z score is useful as it can:
- Classify the sizes and ages’ children equally,
- Pinpoint the given height and weight with respect to reference values,
- Apply to the population and individuals.
How to find Z Score in excel?
You can use the formula to calculate the z-score, that is:
| z = (x-μ)/σ where, μ is the population mean, x is the raw score, and σ is the population standard deviation. |
As you can see, a z-score is the simple population means that is subtracted from the raw score and divided by the population standard deviation.
In the case of population standard deviation and population mean are unknown, the score can be measured with sample mean and sample standard deviation.
For example, suppose you have a test score of 180 in which the standard deviation (σ) has the value 20 and the mean value (μ) 140. Assume the normal distribution; then the z-score can be calculated as:
z = (x – μ) / σ= (180 – 140) / 20 = 2. |
The z-score helps you to know how many SD from the average your calculated score is. In the given example, the score is 2 SD above the average.
This is how to find Z score in excel with the hand. Apart from this, there are different methods by which you can easily calculate the z-score:
- Excel
- Tl-89
Below, we have explained the excel methods. This will help you to understand the methods for how to find z score or how to calculate z score in excel. So, let’s check the methods.
Steps on how to find z score in excel
In the below picture, there is many students’ score:
Now, you can calculate the z-score with the help of the average and standard deviation of the data given in the excel datasheet.
To calculate average
Step 1: Click on the formula tab and select the More Function option that is given below the function library section.
Step 2: Now, you need to click on the given category of statistical functions by the drop-down list method. This will display a list of various functions, but you need to select the Average function.
Step 3: You will find that there is a Function Argument dialog box. Here, you need to enter the specific Range from B4:B13 Cells below the given field Number1 and select the OK option.
This is how you can calculate the Mean or Average value.
To calculate Standard Deviation (SD)
Step 1: You need to go to the option formula. Select the More Function that is given under the Function Library section.
Step 2: Select the Statistical Function category that is given in the list. It will provide the number of functions list. Select STDEVPA that is given in the below section.
Step 3: It will display a dialog box of Function Arguments. Enter the value in the cells that range from B4:B13 and select OK.
- This is how you can calculate the standard deviation (SD).
Now, let’s check how to find z score in excel
Step 1: Go to the option formula. Select the more functions option given in the function library section.
Step 2: You will find that there is a drop-down list of various functions. But, here, you have to select the Statistical function given in the list. Now, select Standardize function from the given list.
Step 3: A dialog box of function arguments will be open. Enter the required cell value B4 below the X field.
Step 4: Mention the Average value within the 2nd field that is considered under B15 Cell.
Step 5: Get the SD value in the 3rd column Standard_dev that is given below Cell B16. Select OK.
This is what you get as a result:
- Now, you do not need to write the formula for each row. Just drag the formula, and it will display all Z score values in excel.
| Key points about the above example If you check the data, then you will find that the highest value of the z-score is 2.082778. This is Nick Brown’s z-score, who gets the highest score in the respective exams. In contrast, the smallest value is -0.98521 that is of Adrian Steve. There are both negative as well as positive z-score values. But those students who have got a less score than the average value got a negative z-score. Those students who have got a higher score than the average value got a positive z-score. In the case of the z-score is zero, it means the student(s) gets the same score as that of the average value. |
Important Tip: When you enter the formula, you can easily use it several times repeatedly. Just write the new standard deviation, mean, and X value to the required boxes.
Summary
- Z-score is used to tell the standard deviation value, which is far from the average of the dataset and distribution.
- If the data value is higher than the average value, then the z-score value will consider as the positive value.
- But, if the data value is lower than the average value, then the z-score will consider as the negative value.
- To carry out the statistical analysis, the z-score value can be used.
Hope this blog on how to find z score in excel will help you. But if you still have any doubt or you are stuck somewhere. In that case, you can comment in the below section. We will help you in the best possible way. So, keep learning new things every day and keep practicing to polish your skills. we also provide help with excel homework at reasonable rates. our excel homework assignments give you the best service.
Also Read
- A Guide On Frequency Distribution Excel For Beginners
- Learn About Excel & Topics of Excel Assignment For Students
- How to Find Range in Excel
Frequently Asked Questions
What is a Z score in statistics?
It is a numerical measurement used to describe the mean value relation with the mean of a group. Z score is calculated as the standard deviations from the mean.
Can the z-score be negative?
Yes, the z score would be negative that indicates that it is below the mean value. But here, the probability or the area can not be zero.
