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
BusinessFinanceFinTech

How to Calculate DeadWeight Loss

2 Mins read
Contents1 Steps to Learn How to Calculate DeadWeight Loss1.1 Price Floors1.2 Price Ceiling1.3 Taxation1.4 Subsidies The term ‘deadweight loss’ refers to the…
Finance

4 Steps To Investing In A Precious Metal IRA

3 Mins read
Ah, retirement… that great period of our lives during which we don’t have to work at all while we are financially set….
BusinessFinance

Your Guide to Understanding The Different Kinds of Loan

3 Mins read
Contents1 Types of Loans1.1 Unsecured Loan 1.2 Commercial Loan1.3 Secured Loan 1.4 Personal Loan 1.5 Urgent Loan1.6 Home Equity Line of Credit (HELOC) 1.7 Business Loan 1.8…
Power your team with Bedoper News

We never spam, only news will be delivered.

Leave a Reply

Your email address will not be published. Required fields are marked *