# How to Find Z Score in Excel

/
/
/
149 Views

Z-score is a statistical measurement that indicates how many standard deviations a given value is from the mean of a data set. It is a useful measure to understand the distribution of a data set and to identify outliers. In this blog post, we will learn how to find the z-score in Excel. We will go through step-by-step instructions, examples, and explanations to make it easy to understand.

## Part 1: Understanding Z-Score

Before we learn how to find the z-score in Excel, let’s understand what z-score is and how it is calculated.

Z-score is calculated using the following formula:

Z = (X – μ) / σ

Where,

X is the value for which you want to find the z-score μ is the mean of the data set σ is the standard deviation of the data set

The z-score measures the distance between a data point and the mean in units of standard deviation. A positive z-score means the data point is above the mean, and a negative z-score means the data point is below the mean.

For example, let’s say you have a data set with a mean of 50 and a standard deviation of 10. A value of 70 would have a z-score of 2 (i.e., two standard deviations above the mean), and a value of 30 would have a z-score of -2 (i.e., two standard deviations below the mean).

### Part 2: Finding Z-Score in Excel

Excel has built-in functions that can calculate the z-score for a given data set. There are two functions that can be used to calculate the z-score in Excel:

1. Z.TEST Function
2. NORM.S.INV Function

Let’s explore each of these functions in detail.

1. #### Z.TEST Function

The Z.TEST function is used to calculate the z-score for a hypothesis test. It returns the probability that the observed sample mean is greater than or equal to the population mean.

The syntax for the Z.TEST function is as follows:

Z.TEST(array, x, [sigma])

Where,

Array is the range of the data set X is the value for which you want to find the z-score Sigma is the standard deviation of the data set (optional)

#### Example:

Suppose we have a data set of exam scores, and we want to find the z-score for a score of 75. The data set is in the range A1:A10, and the mean is in cell B1, and the standard deviation is in cell B2.

To find the z-score for a score of 75, we can use the following formula:

=Z.TEST(A1:A10,75,B2)

This formula will return the z-score for a score of 75 in the data set.

1. #### NORM.S.INV Function

The NORM.S.INV function is used to calculate the z-score for a given probability. It returns the value for which the cumulative distribution function of a standard normal distribution is equal to a given probability.

The syntax for the NORM.S.INV function is as follows:

NORM.S.INV(probability)

Where,

Probability is the probability for which you want to find the z-score

#### Example:

Suppose we want to find the z-score for a probability of 0.95. To do this, we can use the following formula:

=NORM.S.INV(0.95)

This formula will return the z-score for a probability of 0.95.

Part 3: Examples

Let’s go through some examples to see how to find the z-score in Excel.

#### Example 1:

Suppose we have a data set of 100 exam scores with a mean of 75 and a standard deviation of 10. We want to find the z-score for a score of 85.

To find the z-score for a score of 85, we can use the following formula:

Z = (X – μ) / σ

Z = (85 – 75) / 10

Z = 1

Therefore, the z-score for a score of 85 is 1.

#### Example 2:

Suppose we have a data set of 50 heights with a mean of 170 cm and a standard deviation of 10 cm. We want to find the z-score for a height of 185 cm.

To find the z-score for a height of 185 cm, we can use the following formula:

Z = (X – μ) / σ

Z = (185 – 170) / 10

Z = 1.5

Therefore, the z-score for a height of 185 cm is 1.5.

#### Example 3:

Suppose we have a data set of 200 salaries with a mean of \$50,000 and a standard deviation of \$10,000. We want to find the z-score for a salary of \$70,000.

To find the z-score for a salary of \$70,000, we can use the following formula:

Z = (X – μ) / σ

Z = (70,000 – 50,000) / 10,000

Z = 2

Therefore, the z-score for a salary of \$70,000 is 2.

#### What is a z-score, and what does it measure?

A z-score measures the distance between a data point and the mean in units of standard deviation. It is a statistical measurement that indicates how many standard deviations a given value is from the mean of a data set.

#### Why is the z-score important?

The z-score is important because it provides a standardized measurement of how far away from the mean a data point is. This can be used to identify outliers and to understand the distribution of a data set.

#### How do you interpret a z-score?

A positive z-score means the data point is above the mean, and a negative z-score means the data point is below the mean. The higher the absolute value of the z-score, the further away from the mean the data point is.

#### What is the difference between the Z.TEST function and the NORM.S.INV function in Excel?

The Z.TEST function is used to calculate the z-score for a hypothesis test, while the NORM.S.INV function is used to calculate the z-score for a given probability.

#### Can the z-score be negative?

Yes, the z-score can be negative if the data point is below the mean.

#### What is a good z-score?

A good z-score depends on the context of the data set. In some cases, a z-score of 1 may be considered significant, while in others, a z-score of 3 or more may be required to be significant.

#### How do you use the z-score to identify outliers?

Data points with a z-score greater than 3 or less than -3 are often considered outliers.

#### How is the z-score related to the standard deviation?

The z-score is calculated using the formula Z = (X – μ) / σ, where σ is the standard deviation of the data set.

#### Can the z-score be greater than 1?

Yes, the z-score can be greater than 1 if the data point is more than one standard deviation away from the mean.

#### Can the z-score be used for non-normal distributions?

Yes, the z-score can be used for non-normal distributions if the data set is large enough and the Central Limit Theorem applies.

## Author

• Meet Jane, a passionate blogger with a love for all things creative. From DIY projects to healthy recipes, Jane enjoys sharing her ideas and experiences with her readers. She believes that everyone has a unique story to tell, and hopes to inspire others to explore their creativity and pursue their passions. Follow along as Jane shares her journey and tips for living a fulfilling life.

This div height required for enabling the sticky sidebar