How To Calculate A Z Score In Excel

In statistics, a z-score tells u.s.a. how many standard deviations away a value is from the mean. We use the post-obit formula to calculate a z-score:

z = (X – μ) / σ

where:

X is a single raw data value
μ is the mean of the dataset
σ is the standard deviation of the dataset

This tutorial explains how you tin calculate z-scores for raw data values in Excel.

How to Calculate Z-Scores in Excel

Suppose we take the post-obit dataset and nosotros would similar to find the z-score for every raw data value:

Z score dataset in Excel

We can perform the following steps to do so.

Step 1: Discover the mean and standard difference of the dataset.

First, we need to find the hateful and the standard deviation of the dataset. The following formulas evidence how to practice so:

Z score calculation in Excel

The hateful turns out to exist 14.375and the standard deviation turns out to be four.998.

Step 2: Notice the z-score for the first raw data value.

Side by side, we'll find the z-score for the first raw data value using the formulaz = (10 – μ) / σ.

Jail cell C2 shows the formula we used to calculate the z-value in cell B2.

Z score calculation in Excel

Step three: Discover the z-scores for all remaining values.

Now that we found the z-score for the first value in the dataset, nosotros can simply copy the formula we used in prison cell B2 to the rest of the data values. We can practice this by highlighting the entire z-score column, starting with the outset z-score we already calculated:

Z scores in Excel

Side by side, press Ctrl+D. This copies the formula in the showtime cell to all of the cells below information technology.

Z score for raw data values in Excel

Now, we have found the z-score for every raw information value.

How to Interpret Z-Scores in Excel

Recollect that az-scoresimply tells us how many standard deviations away a value is from the mean.

A z-score tin can be positive, negative, or equal to cipher.

A positive z-score indicates that a particular value is greater than the mean, a negative z-score indicates that a particular value is less than the hateful, and a z-score of zero indicates that a particular value is equal to the hateful.

In our instance, we found that the hateful was14.375and the standard deviation was4.998.

And so, the first value in our dataset was vii, which had a z-score of (7-14.375) / 4.998 =-1.47546. This means that the value "vii" is -1.47545 standard deviationsbelowthe mean.

The adjacent value in our information, 12, had a z-score of (12-14.375) / 4.998 =-0.47515. This means that the value "12" is -0.47515 standard deviationsbeneaththe mean.

Z score for raw data values in Excel

The further away a value is from the mean, the higher the absolute value of the z-score will be for that value.

For example, the value seven is farther abroad from the mean (14.375) compared to 12, which explains why 7 had a z-score with a larger accented value.