Z-score in Excel: Z-score is an important statistical indicator for comparing observations. That is why many statisticians and researchers want to know how to find Z score in excel.
The z-score is used in research to compare findings from different populations using the mean and standard deviation. A z-score is also used to measure market volatility.
So, let’s learn about the z-score and how to calculate it in Excel. And get straight to the details.
What is a z-score?
A z-score compares the values of two or more data sets. It is also the standard deviation of a data point from the average. The function that computes a z-score is:
=[MEAN (DataSet)] Stddev (DataSet)
Let us provide an example to better understand z-score. Assume you need to compare two students’ Algebra results from two separate teachers. In their individual exams, student one achieved 95% and student two 87%.
When comparing the two scores, it is clear that the 95% score is superior. It’s possible that the second teacher offered a difficult exam. The z-score of each student is based on the class mean and standard deviation.
Students who got 87% may have performed better than students who scored 98 percent, based on their z-scores.
So, why are z-scores important?
It is usually desirable to normalize the normal distribution’s raw score (value) into the z-score as:
This allows researchers to assess the score’s likelihood of occurring in the normal distribution.
- It also compares multiple (two) scores from distinct samples (with different standard deviation and mean).
What is the z-score used for?
The WHO/NCHS reference values necessitate a precise approach to assess baby and early child nutrition. The WHO expert team suggested the new reference concern for child and newborn weight and height.
The three referencing methods are percentage of median, z score, and percentiles.
- Z-scores: they reveal how far a child’s weight is from the reference data median.
In percentages, an individual’s weight (for a given height) is compared to the reference values.
The z score is useful because it can:
- Sort children by size and age evenly.
- Measure the specified height and weight against reference values.
- Population and individuals.
Z Score in Excel –
The z-score is calculated as follows:
z = (x-)/
x is the raw score, and s is the population standard deviation.
As you can see, a z-score is the population mean divided by the population standard deviation.
When the population standard deviation and mean are unknown, the score is calculated using the sample mean and standard deviation.
Assume you have a test score of 180 with a standard deviation of 20 and a mean of 140. Assuming a normal distribution, the z-score is as follows:
(180 – 140) / 20 = 2
The z-score tells you how far your computed score is from the average. In this case, it is 2 SD above average.
How to find Z score in Excel by hand. Besides this, there are several ways to calculate the z-score:
Below are the excel approaches. This will help you learn how to find z score or calculate z score in excel. And now the ways.
How to find the z score in Excel
Below is a picture of various students’ scores:
You can now compute the z-score using the average and standard deviation of the data in the excel spreadsheet.
Step 1: Select the More Function option below the function library area on the formula tab.
Step 2: Select the statistical function category from the drop-down list. This will show a list of functions, but you must select Average.
Step 3: A Function Argument dialog box appears. Enter the range B4:B13 Cells below the field Number1 and click OK.
This is how the Mean or Average is calculated.
Calculate SD (SD)
Step 1: Open the option formula. Select More Function from the Function Library area.
Step 2: Choose Statistical Function from the list. It will provide a list of functions. Select STDEVPA from the list below.
Step 3: A Function Arguments dialog box appears. Enter the value in B4:B13 and click OK.
- How to calculate standard deviation (SD).
Learn how to find z score in Excel.
Begin at the option formula. Select More Functions from the Function Library menu.
Step 2: A drop-down menu of functions will appear. Just pick one of the statistical functions from the list. Select Standardize from the list.
Step 3: A function argument dialog box will open. Enter B4 below the X field.
Step 4: Mention the Average value in the second field under B15 Cell.
Step 5: Find the SD value in the Standard dev column below Cell B16. OK.
As a result, you get:
- No longer write the formula for each row. Drag the formula to see all Z scores in excel.
Notes on the above example
If you look at the data, the highest z-score is 2.082778.
Nick Brown’s z-score, who tops the exams.
The smallest value is -0.98521, which is Adrian Steve’s.
The z-score might be negative or positive.
Students who scored below the average had a negative z-score.
Students who scored higher than the average had a positive z-score.
If the z-score is 0, the student(s) has the same score as the average.
Important Tip: When entering the formula, you can easily repeat it. Simply update the standard deviation, mean, and X values.
- The Z-score tells the standard deviation, which is far from the dataset and distribution average.
- If the data value is higher than the average, the z-score is positive.
- If the data value is less than the average, the z-score is negative.
- The z-score value can be utilized for statistical analysis.
I hope this blog about finding z score in excel helps. But if you are still unsure or stuck. In such case, please leave a comment below. We will do our best to assist you. So, keep learning new things and honing your talents. We also offer affordable Excel assignment help. Our excel homework help is the best.
How To Use Excel For Frequency Distribution
- Learn Excel & Topics for Students
- Excel Find Range
Questions & Answers
What is a Z score?
It is a numerical measure of a group’s mean value relation. The Z score is the standard deviation from the mean.