EducationFinanceMarketing

How to Calculate Z Score In Excel

2 Mins read

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.

How to Calculate Z Score In Excel
Dataset of marks of students

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. 

How to Calculate Z Score In Excel
Calculation of average or 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. 

Z Score In Excel
Calculation of standard deviation

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. 

excel screenshot
Calculation of Z score

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.

RECOMMENDED TUTORIAL

About author
I am an undergraduate student with a passion of writing. I hope my articles will help you out.
Articles
Related posts
Education

Guide for Students on How to Write a Master's Thesis

2 Mins read
Contents1 Get thesis writing help2 Start work early3 Pick your topic out of passion4 Dedicate enough quality time5 Use diverse reference materials …
BusinessMarketing

All you need to know about replaying sessions

2 Mins read
Every online analytics solution exists to tackle the same problem: to give actionable, trustworthy, and fast user information. Understanding the online person…
Marketing

Top Advertising Methods to Follow in 2022

2 Mins read
Contents1 Newspapers/Magazines2 TV3 Radio4 Cinema5 Billboards When it comes to growing a business, advertising and marketing is hugely important in terms of…
Power your team with Bedoper News

We never spam, only news will be delivered.

Leave a Reply

Your email address will not be published.