How To Find Z Value In Excel

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

z = (10 – μ) / σ

where:

X is a unmarried raw information value
μ is the mean of the dataset
σ is the standard deviation of the dataset

This tutorial explains how you lot can calculate z-scores for raw information values in Excel.

How to Calculate Z-Scores in Excel

Suppose we accept the post-obit dataset and we would similar to notice the z-score for every raw data value:

Z score dataset in Excel

We tin can perform the following steps to exercise so.

Step 1: Find the mean and standard divergence of the dataset.

First, nosotros need to find the mean and the standard deviation of the dataset. The post-obit formulas show how to do so:

Z score calculation in Excel

The mean turns out to be 14.375and the standard deviation turns out to be 4.998.

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

Next, nosotros'll detect the z-score for the first raw data value using the formulaz = (X – μ) / σ.

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

Z score calculation in Excel

Stride 3: Detect the z-scores for all remaining values.

Now that we constitute the z-score for the first value in the dataset, nosotros can simply copy the formula we used in cell B2 to the rest of the data values. We tin practise this past highlighting the unabridged z-score cavalcade, starting with the first z-score nosotros already calculated:

Z scores in Excel

Next, press Ctrl+D. This copies the formula in the first cell to all of the cells below information technology.

Z score for raw data values in Excel

Now, we accept plant the z-score for every raw data value.

How to Translate Z-Scores in Excel

Recall that az-scoresimply tells u.s. how many standard deviations away a value is from the mean.

A z-score can exist positive, negative, or equal to zero.

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

In our example, we institute that the hateful was14.375and the standard divergence wasiv.998.

So, the showtime value in our dataset was seven, which had a z-score of (7-14.375) / 4.998 =-ane.47546. This ways that the value "7" is -1.47545 standard deviationsbelowthe mean.

The side by side value in our information, 12, had a z-score of (12-fourteen.375) / 4.998 =-0.47515. This means that the value "12" is -0.47515 standard deviationsbelowthe mean.

Z score for raw data values in Excel

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

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