Z score which is commonly known as standard score is the statistical measurement that calculates how far the actual score deviates from the mean in standard deviation. Here’s How to Calculate Z Score In Excel, It shows how far a data point is from the mean. Z score compares the result to a normal population. For example, we had information about someone’s weight, that is, 120 pounds. It might be useful information but if we want to compare it with average human weight, the data can be overwhelming. Here , z score will help us to find where that person’s weight lies compared to the average population weight.
Z score can be easily calculated by using a simple formula
Z = (x-µ) / σ
The important information to calculate z score is mean and standard deviation of the dataset. The formula states that the z score can be calculated by finding the difference of data point with mean of the dataset by standard deviation.
In the above formula,
Z = Z score
X = Data point
µ = Mean of the given dataset
σ = Standard deviation of the given dataset
Calculating z score manually is indeed simple but what if we have a huge dataset? It would take hours and hours of calculation yet the result probably has some errors. In order to handle large datasets we can use excel. Excel has standardized formulas that can be used for calculating z score in seconds. Below are the few steps explaining how to calculate z score in excel.
DETERMINE THE DATASET
We have considered the following dataset with 10 entries. Now for the calculation of z score we need to find the mean and standard deviation.
MEAN AND STANDARD DEVIATION
For the calculation of z score we first need to calculate the mean of the data. For this purpose, a simple AVERAGE formula can be used in excel. In this formula, we need to specify the row no.
To specify the formula, go to the Formula tab and click on More Functions. Now, click on Statistical Function in the drop down list, it will again open a list of functions. Select the AVERAGE function. A dialogue box will appear, here we need to specify the starting and end row to calculate the mean.
We have specified the row A2 till A11. And the final result is 73. For standard deviation, in the same statistical function, select the ‘STDEV.P’ and again specify the rows, that is, A2 till A11.
Calculation for How to Calculate Z Score In Excel
For the calculation of z score, we have added another column named as Z SCORE. To specify the formula for z score, navigate into the formula tab. Select More Function and go into Statistical function. Select the STANDARDIZE function from the drop down list. It will ask for the X, Mean and standard deviation. So you have to specify the value row. For example, for the first value, X would be A2, Mean or Average would be C13 and standard deviation would be C14. And for the other rows, just drag down the formula and it will automatically calculate the remaining z score values.
ANALYSIS for How to Calculate Z Score In Excel
- A person who has scored the lowest has the lowest z score value.
- A person who has scored the highest has the highest z score value.
- There can be positive and negative values.
- Zero represents that the person has scored the same marks as the mean value.