how to find z score in excel

Excel Z Score (Tabular array of Contents)

Z Score in Excel
How to Summate Z Score in Excel?

Z Score in Excel

Z Score is used for statistical measurement. It is also known equally a standard score. The value of the Z score is the measurement of the number of standard deviations a specific number is higher up or below a hateful.

The beneath formula is used to calculate the Z score:

Z = (x- µ) / σ

Where the supplied arguments are as below:

Z = Information technology denotes the Z score value.
10 = The value to be standardized.
µ = Mean of the given information set up values.
σ = Standard divergence of the given data ready values.

How to Calculate Z Score in Excel?

Calculation of Z Score in excel is very simple and piece of cake. Let'due south sympathize how to calculate the Z score in excel with some examples.

You can download this Z Score Excel Template here – Z Score Excel Template

Excel Z Score – Example #i

We accept given marks of some students as below:

Now for calculating Z Score, we need to notice out the Mean and standard deviation of the given dataset in excel.

Mean (or Boilerplate) calculation:

For finding the boilerplate, follow the below steps:

Step 1 – Go to the Formulas tab. Click on More than Functions options nether the Functions Library department.

Step 2 – At present click on the Statistical functions category from the drop-downwardly list. Information technology volition once more open up a list of functions. Click on the Average function as shown below.

Step 3 – Information technology will open up a Function Arguments dialog box. Enter the Range from Cells B4:B13 under field Number1 and click on OK.

It volition give yous the Average or Mean value.

Standard Deviation calculation:

For finding the standard deviation, follow the below steps:

Step 1 – Get to the Formulas tab. Click on More Functions nether the Part Library department.

Step 2 – Click on the Statistical Office category from the drop-downwardly listing. It volition open a listing of functions. Click on STDEVPA from the list as shown in the below screenshot.

Step 3 – Information technology will open a Function Arguments dialog box. Enter the cells range from B4:B13 under field Value1 and click on OK.

It will give yous the standard divergence value.

At present we have to calculate the Z score values in excel. For this, follow the below screenshots:

Go to the FORMULAS tab. Click on the More Functions choice nether the Function Library section.

And then a drop-down list of functions will open up. Click on Statistical functions from the list. Information technology will open a list of functions. Click on STANDARDIZE function from the list as per the below screenshot.

Then it volition open up a Function Arguments dialog box. Enter the Jail cell value B4 under field Ten.

Enter the Mean value in the second field, Mean, which is mentioned under Cell B15.

Enter the Standard Deviation value in the third field, Standard_dev, which is mentioned under Cell B16. Click on OK.

The result volition be as given below.

Elevate this formula for the rest values, and it will pop upwardly the Z score values in excel equally shown beneath:

Explanation

If we analyze the data, the highest Z score value is ii.082778, which is the Z score value of Nick Chocolate-brown, who has achieved the highest score in the exam.
The smallest Z score value is -0.98521, which is the lowest Z score value of Adrian Steve, who has achieved the lowest score in the test.
There can be positive and negative values in Z scores.
Students who have accomplished scores more than than the mean value get positive Z scores.
Students who have accomplished scores less than the mean value get negative Z scores.
If the Z score is aught, that ways the pupil'south score is the same as the mean value.

Excel Z Score – Case #2

We accept given the below data values.

To summate the Z score or standard score, we demand to make up one's mind the offset mean and standard divergence in excel.

Let's apply the AVERAGE formula for calculating the hateful of the given dataset.

It will give you the Average or Mean value.

For calculating standard difference, allow'due south apply the STDEVPA part for the given data values equally per the below screenshot.

It will give you the standard departure value.

Now we will summate Z score values in excel. For this, apply the STANDARDIZE function for the given data values as per the beneath screenshot.

The result volition be every bit given below.

Elevate this formula for the rest values. The Final event is given below:

As we tin encounter, the positive value of Z scores are higher than the mean value, and the negative value of Z scores are lower than the hateful value.

Things to Remember

The Z score tells united states a number of standard deviations that are abroad from the mean of the distribution or dataset.
The information values which are higher than the mean Z score will exist a positive value.
Information values which are below the hateful Z score volition exist a negative value.
The z score value is probably used for statistical analysis.

Recommended Manufactures

This has been a guide to Z Score in Excel. Hither we discuss how to calculate Z Score in excel along with practical examples and a downloadable excel template. You lot tin can also become through our other suggested articles –