Introduction

As mentioned on the previous page, we can use those z-score tables to find probabilities. But we can also use Excel to find them much faster and accurately! The purpose of this page is meant to introduce these ideas for both the normal distribution and the standard normal distribution.

Again, here is a document with the z-score tables from UPenn!

What You Will Do

Make sure you read through all of the information below and watch the videos that are embedded within this page. The information written here is meant to help introduce concepts, and the videos are meant to help show examples and reinforce ideas about these concepts, so both are necessary to help you learn!

Z-score tables or Excel Function?:

We can use z-score tables to find [rounded] probabilities. We can also use Excel functions to find them for us! Excel can find these probabilities SO QUICKLY and actually gives us exact probabilities instead of rounded ones! Either way is fine, and both ways are important to be aware of!

We will learn this in an upcoming Challenge, but if you wanted to get a head start on practicing them, I have included them in this page!

Finding probabilities LESS THAN:

To find the probability less than a value, we can use the z-score tables (as shown in the video below).

When we say "area to the left" we really mean the "probability less than" that value. This is shown in the image below.

This diagram shows a bell-shaped curve with uppercase X at the extreme right end of the X axis. The X axis also contains a lowercase x about one-quarter of the way across the X axis from the right. The area under the bell curve to the right of the lowercase x is shaded. The label states: shaded area represents probability P(X < x).

Example with $LaTeX: P\left(X<-1.43\right)$

Example: If you want the probability to the left of the z-score -1.43, we can find this by doing:

$LaTeX: =NORM.S.DIST\left(-1.43,TRUE\right)$ . If you do this, you should get 0.0763585...

My function: screenshot of using =norm.s.dist() in Excel after I hit "Enter": results of using =norm.s.dist() function in Excel

Again, here is a document with the z-score tables from UPenn!

Now you!

Watch this video from Khan Academy about how to find the probabilities less than a value using the z-score tables:

Finding probabilities GREATER THAN:

If we wanted to find the probability to the RIGHT of -1.43, since the total area under the curve has to be 1, we can subtract the value we found above from 1!

That is, $LaTeX: P\left(X>-1.43\right)=1-NORM.S.DIST\left(-1.43,TRUE\right)$ which would be the same as 1-0.076358... and we would get 0.92364...

I typed in: example of finding the probability greater than z=-1.43 after I hit "Enter": after hitting enter in Excel for =1-Norm.S.Dist() function

Again, here is a document with the z-score tables from UPenn!

Now you!

Watch this video from Khan Academy about how to find the probabilities greater than a value using the z-score tables:

Finding probabilities BETWEEN two z-scores:

If we wanted to find the probability BETWEEN two z-scores, we need to use this function twice!

If we use $LaTeX: =NORM.S.DIST\left(1.26,TRUE\right)$ this finds the area/probability to the left of 1.26.

If we use $LaTeX: =NORM.S.DIST\left(-1.43,TRUE\right)$ this finds the area/probability to the left of -1.43.

Since want the area between these two z-scores, we can subtract these probabilities (making sure to put the higher one first). That is, we can do:

That is, $LaTeX: P\left(-1.43<X<1.26\right)=NORM.S.DIST\left(1.26,TRUE\right)-NORM.S.DIST\left(-1.43,TRUE\right)$ which would give us a probability/area of 0.8198068...

I typed in: example of finding the area between two z-scores in Excel after I hit "Enter": Results of calculating the area between two z-scores in Excel

Again, here is a document with the z-score tables from UPenn!

Now you!

Watch this video from Khan Academy about how to find the probabilities between two values using the z-score tables:

Remember:

P ( X < x ) is the area to the left of the vertical line through x
P ( X > x ) = 1 – P ( X < x ) is the area to the right of the vertical line through x
P ( a < X < b ) = P(X<b) – P ( X < a ) is the area between a and b
P ( X < x ) is the same as P ( X ≤ x ) and P ( X > x ) is the same as P ( X ≥ x ) for continuous distributions

In Excel, we do NOT need to convert to z-scores!

If you are using the z-score tables, if you are given a distribution with a mean and standard deviation other than 0 and 1 (respectively), you need to do a lot of conversions to z-scores to use the tables. Excel will do all of this for you and look up the probability without you having to!

This function is $LaTeX: =NORM.DIST\left(x,\mu,\sigma,TRUE\right)$ . (The only difference between this one and the one from above is the "S" in the middle, which stands for "Standard".) For this one, you will need to enter the mean and standard deviation.

This function takes your information, finds the z-score, and then outputs the correct probability to the left of that value. It works the same way as the =NORM.S.DIST() function in terms of finding area to the left, to the right, and between two values.

For example:

In the United States the ages of smartphone users approximately follow a normal distribution with approximate mean and standard deviation of 36.9 years and 13.9 years, respectively. Let's use Excel functions to find the probability that a random smartphone user is between 23 and 64.7 years old.

The area to the left of 23, or $LaTeX: P\left(X<23\right)$ , can be found by doing $LaTeX: =NORM.DIST\left(23,36.9,13.9,TRUE\right)$

The area to the left of 64.7, or $LaTeX: P\left(X<64.7\right)$ , can be found by doing $LaTeX: =NORM.DIST\left(64.7,36.9,13.9,TRUE\right)$

So, the area between these two values, or $LaTeX: P\left(23<X<64.7\right)$ can be found by doing $LaTeX: =NORM.DIST\left(64.7,36.9,13.9,TRUE\right)$ $LaTeX: -NORM.DIST\left(23,36.9,13.9,TRUE\right)$

What I typed into Excel: and after I hit "Enter":

So, the probability that a random smartphone user is between the ages of 23 and 64.7 is about 0.8186 or 81.86 percent!

Percentiles (again and again!)

To find the percentile of a data value, we are finding the probability/area less than that value. So, as described above, we can use the z-score tables or the NORM.S.DIST() or =NORM.DIST() functions in Excel, as is, to find that area to the left of a value.

But, remember we are often asked things like "What value is at the 80th percentile?" That means we are looking for either an x-value or a z-score that has 80% of the data (or 0.80 of the area) to the left of that value.

Problem:

In the United States the ages of smartphone users approximately follow a normal distribution with approximate mean and standard deviation of 36.9 years and 13.9 years, respectively. What age of smartphone users represents the 80th percentile?

Solution by hand and z-score tables:

Again, here is a document with the z-score tables from UPenn!

We are looking for what value has 80% of the data less than it (and 20% of the data greater than it). One way to do this is to use the z-score tables.

The value we are looking for must be above the mean since it has more than 50% of the data below it. So, we need to use the positive z-score table. See the screenshot below:

positive z-score table

The closest probability to 0.80 that we can see within the inner cells of this table is 0.79955. If you back out to look at which row and column you are in for that probability, it corresponds to a z-score of +0.84. That is, the age that is the 80th percentile has a z-score of 0.84. [See the information below about using the =NORM.S.INV() function in Excel to find this z-score very quickly!]

Then we can use that to solve for what the actual data value is by substituting it into our z-score formula:

So, $LaTeX: z=\frac{x-\mu}{\sigma}$ implies here that $LaTeX: 0.84=\frac{x-36.9}{13.9}$ .

Solving for x, we get 48.576 (remember this is actually the 79.955th percentile, not the 80th percentile). That is, the age of about 48.58 is at about the 80th percentile of all smartphone users.

Solution with Excel Function

Here is a SUPER handy Excel Function: It is $LaTeX: =NORM.INV(probability, \mu, \sigma)$ , where you enter the probability to the left of the value you are looking for. For example, in our problem above, we would enter:

LaTeX: =NORM.INV(0.80, 36.9, 13.9) and we immediately get out 48.59, which is an even MORE exact answer than the one we found above (because all of the probabilities on the z-score table are rounded!).

What I typed into Excel: and after I hit "Enter":

To find a z-score given a probability we use =NORM.S.INV()

There is also this inverse function for the Standard Normal Distribution. So, we could quickly identify the z-score of the 80th percentile for any distribution as LaTeX: =NORM.S.INV(0.80) to get a z-score of about 0.8416.

What I typed into Excel: and after I hit "Enter":

Now you!

Watch this video from Khan Academy about how to find an x-value given a probability using the z-score tables:

Watch this video from Khan Academy giving another example of finding an x-value using the z-score tables:

Make Connections in our Textbook

Read through Section 6.2 and the examples provided to help your understanding!

Read about Using the Normal Distribution in our text

What's Next

You will complete some Metacognition Video Notes, a Progress Check, and a CYU over the material so far.