(upbeat music)
- Hello, I am Professor Rick Jerz,
and I welcome you to this session titled
Normal Probability Distributions Using Excel.
In this session, I'm going to show you
how to use my Excel model for solving normal probability
distribution problems for population data.
I think that you are going to see that my Excel model
makes solving these problems pretty easy.
In fact, much easier than the common textbook methods
of calculating intermediate Z values and reading
and interpreting tables in the back of the book.
Okay, let's get started.
Here's a bird's eye
view of my model for normally distributed data.
I do calculations in this upper-left area.
Below the calculations, I provide a chart showing the "area
under the curve" of interest in red.
To the right, I provide a normal probability table,
just for reference, as many textbook authors include in their textbooks.
This model is going to serve several normal distribution topics.
I have somewhat hidden future topic features by covering them with blue.
Our current topic is addressing "population" normally distributed data.
Although this model might seem a little complex, I designed it
to be flexible in the kinds of questions
that involve the normal probability distribution.
My model calculates normal probabilities for the most commonly asked questions.
It contains direct calculations, avoiding
intermediate Z-value calculations,
and you don't need to read or interpret tables in the back of the book.
The graphic becomes particularly helpful
so that you can see the probability, or area of interest.
This area to the left is titled "Population,"
which is for population normally distributed data, our current topic.
Let's explore this part of the model.
The columns, with headings A, B, and C are
used to answer the three most commonly asked questions.
You enter data in the green cells, and the pink cells are results.
Columns A and B, you should notice, calculate probabilities.
When you have the mean and standard deviation, and want to know
the probability between two specific points, use column A.
In this area, enter the lower and upper values.
Column B is similar, perhaps a special case of column A.
Some questions provided a plus or minus value instead of having a lower and upper value.
For example, what is the probability of being within .35 of the mean?
In these situations, use Column B.
Column C is a little different.
It is the inverse of columns A and B.
In these situations, you know the probability,
but you want to calculate the beginning and ending values that produce
this probability, or the plus and minus value from the mean.
These beginning and ending points define the "confidence interval." Notice
that all three columns, or scenarios, use the mean and standard deviation.
The best way to understand my model is to do some problems.
Let's try this one, Example 1.
Refer to the information regarding the weekly income of shift
foreman in the glass industry.
The distribution of weekly income follows the normal probability distribution
with a mean of $1000 and a standard deviation of $100.
What is the probability of selecting a shift foreman
in the glass industry whose income is between $790 and $1000?
First, we must decide whether to use columns A, B, or C.
Since the question asks, "What is the probability,"
we know that columns A and B calculate probability.
Also, we do not use column B
since this question is not asking "between
plus or minus" or "within." So, we use column A.
In the "plot which condition," this cell is a dropdown, and I can pick A.
This selector causes the graph to know which column to plot.
Enter 790 as the lower value and 1000 as the upper.
Enter $1000 for the mean and $100 for the standard deviation.
Excel calculates the probability to be 48.21%,
which matches the author's calculation.
Notice that the author provides a graphic showing this 48.21%.
If you scroll down to my model's graphic, notice that it is the same.
The 48.21% is the red area under the normal curve.
The author had to calculate the intermediate z-value,
and then used this z-value to read the table in the back of the textbook.
All that we needed to do was to interpret
the question, enter values into Excel, and we were done.
Wow, this is pretty easy.
I hope you can appreciate that my Excel model is much easier and faster!
Let's do another problem.
This is the same problem scenario,
except now we are asked the probability of selecting a shift foreman
in the glass industry whose income is less than $790?
Less than 790 means that the upper value is
790. But what is the lower value?
If you can envision a number line, 0 in the middle, and 790 to the right, "less
than" means any value to the left of 790.
What should we enter as the smallest value?
Well, how about minus infinity?
Hmmm, my model wants a value.
Well, how about some relatively small value, like minus 10,000?
Excel calculates 1.79% for the probability,
exactly the same probability that the author calculates.
You can verify that -10000 is small enough by looking at the chart.
The red area is everything left of 790.
You might also notice that we didn't have to pick -10,000.
A value like 0 would have worked.
In fact, if you remember that three standard deviations
represent about 99% of the curve, three times 100,
or 790 minus 300, 490 would have been small enough.
Once again, the author has to calculate intermediate Z-values
and read the table in the back of the book.
Another subtle problem with tables is that the tables in the back of the book
only show the upper side of the normal probability distribution.
This problem's area is to the left of zero,
so you would have to make this mental adjustment to read the table properly.
We don't have to worry about this in our Excel model.
Wow, this is almost fun solving problems.
We do this in less than a minute, which might take the average student
10 to 15 minutes to solve this problem.
Let's do another problem.
This problem continues our previous scenario, except
we want the probability of the income between
$900 and $1100.
Ah, 900 is the lower value, 1100 is the upper.
Just like the first problem, I enter these two values,
and Excel calculates the probability as 68.27%.
If you are pretty good with numbers, you might notice that this $900 to $1100
is the equivalent of saying the mean, 1000, plus or minus $100.
So, we could have used column B.
I'll show you by switching "Plot which condition" to B, entering 1000 for
the plus or minus X, 1000 for the mean, and 100 for the standard deviation.
We get exactly the same probability of 68.27%.
This problem illustrates a plus or minus situation.
You might also recall that the empirical rule states
that plus or minus one standard deviation away from the mean
is about 68% of the area under the normal curve.
If we were using the table in the back of the book approach,
we would have to calculate the probability on one side of the mean
and then realize that, since this is a plus or minus
kind of problem, we would have to double the table value.
But with our Excel model, we don't have to worry about this.
I hope that you are starting to appreciate the power behind my Excel models.
Let's do another problem.
This problem is the same scenario, with a different question.
The question is, what is the probability of selecting a shift foreman
in the glass industry whose income is between $1150
and $1250.
This is like our first question that contained a lower and upper value.
We use my model's column A, type these two values into the cells,
and see that the probability is 6.06%.
This matches the author's result.
Make sure to have "plot which condition" set to A if you want to look at the chart.
Hmmm, for us, this problem is just like the first,
except the lower and upper values are different.
Why would the author give us another similar question
that we solve very quickly?
Well, if you look at the chart, notice that the red "area
under the curve" is only on the right side.
If we were using the traditional approach
to solving this problem, we would have to use the table twice.
Yes, calculate two z-values, two probabilities, and realize
that we subtract the probability for $1150
from the probability for $1250.
Yep, a bit tricky, perhaps awkward, and definitely more time-consuming.
With my Excel model, no problem.
This type of problem is quickly and easily solved.
Let's do another problem, getting away from these shift foreman problems.
I slightly modified this textbook question.
Layton Tire and Rubber Company wishes to understand
how many miles its new MX100 tire should last.
Tests reveal the mean mileage is 67,900
with a standard deviation of 2,050 miles
and that the distribution of miles follows the normal probability distribution.
Layton wants to know the mileage that 80% of the tires should be between.
To use my model for population data, we assume that Layton Tire
has been collecting data for a while and that when the data is plotted,
it takes the shape of a normal distribution.
We could not use my model if this plotted data
looked like a uniform distribution or some other distribution.
We are told in this problem that the plotted data follows a normal
probability distribution, so we are in good shape using my model.
Another thing that we should notice is that the problem provides a probability
of 80%, which suggests we use my model's column C.
So, I switch "plot which condition" to C, enter the mean,
standard deviation, and this 80% probability.
The answer is that we should expect 80% of the tires to last
between 65272 miles
and 70527 miles.
We can scroll down to see this represented on the chart.
If somebody were to say, how about 90%?
We simply change the .8 to .9 in the probability
cell, and everything gets immediately recalculated.
Well, this is pretty nice.
If we were using tables in the back of the book,
this problem could take us, oh, maybe a half-hour to solve.
Using my model, we solve this problem in less than 15 seconds.
In this problem, we see the term "confidence interval" used.
We will explore "confidence limits" in more detail in a future session.
Here is another form of this same question.
We're provided the same information, but the question asked is slightly different.
Layton Tire wants to set a minimum guarantee mileage
so that no more than 4% of their tires will have to be replaced.
Consider the real business scenario.
Let's say that Layton guarantees that every tire will
last, oh, 300,000 miles.
Wouldn't Layton's have to replace every tire, meaning 100%?
This 300,000 is much too high.
How about a guarantee of 2 miles, then
no tire, or 0%, would be replaced.
However, customers would say, "Your mileage guarantee of 2
miles is meaningless.
I am going to buy my tires from a different company
with a better guarantee!" Or consider a mileage
guarantee of 67,000 miles.
With this mileage guarantee, half the tires, or 50%, would be replaced.
Layton only wants to replace 4% of the tires,
so 67,000 miles is too high?
How about 30,000?
20,000? 5,000?
As you can see, we don't want to guess at a value.
We want to calculate it based upon what we know.
And what we know is that from the tire's history, the mean is 67,000,
the standard deviation is 2050 miles,
and the distribution is "normal." This problem represents
a class of problems described as "one-sided confidence intervals."
Typically, a confidence interval problem is two-sided,
having a plus or minus value from the mean, but in this case,
we want this 4% to be to the left side of our normal probability distribution.
Typically, these problems are tough to solve,
but I have built this one-sided feature into my Excel model.
First, enter the 4% into the model as the probability.
Keep "plot which condition" set to C.
Changing 80% to 4% still centers the red area around the mean.
We don't see this in the chart because 4% is very small.
If you changed 4% to 10%, experimenting, you would see this.
We don't want this data centered, we want it to the left side
of the normal distribution.
I have built a little switch into my model, right in this cell.
Notice that my model defaults to C, meaning
that the area of interest should be centered on the mean.
But I can switch this from C to either L or R, meaning left or right.
In our case, I'm going to pick L.
Now, look at the chart below.
This 4% is moved to the left.
It is no longer centered.
Return to our calculation area.
We see that the range goes from minus infinity
up to 64311.094 miles.
Let's round this up to 64312 miles.
We are done.
If Layton Tires guarantees their tires to 64312
miles, only 4% would likely need replacement.
This matches the author's result.
Realistically, we would never use a value this precise.
Customers would not understand it.
For marketing purposes, Layton could round it up or down
to a better appearing number, maybe 65000 miles.
Wow, my model allows you to do these one-sided questions very quickly.
It's a little tricky, but the more problems you practice,
the better you will understand these features.
Furthermore, we can quickly do "What-if" analysis using this Excel model.
And you can even use more than one column to explore relationships.
For example, we might want to enter zero and 65000 miles into the A condition
along with the appropriate mean and standard deviation.
I see that we would be replacing 7.86% of the tires on average.
This percentage is too high.
Try entering 64,000.
There we go.
If Layton guarantees 63000 miles, less
than 3% of the tires, on average, will require replacement.
Okay, let's do one more problem to illustrate
another way of using my Excel model.
Let's assume I have this data representing students' scores on an exam.
And let's say I want to give a D to the lowest
5% and an A to the upper 5%.
What should these breakpoints for an A and D be?
Okay, we should be able to do this problem,
but what are the mean and standard deviation?
Don't fret.
Recall that we know how
to calculate these statistics quickly using one of my other Excel models,
the model for "Descriptive Statistics." Let me show you.
I have entered this data into the descriptive statistics model,
which calculates the mean and population standard deviation.
Note that our current topic is population data,
so these grades represent our entire class population grades.
Now we return to our normal probability model.
For the mean and standard deviation, I could write
these values down from our descriptive statistics model,
but instead, I'm going to use Excel to reference these values.
Excel can reference other cells in this worksheet,
in other worksheets, and even in other Excel files!
In this cell for the mean, I enter "equal" and then I click
on the descriptive statistics model, and click on the calculated mean.
For the standard deviation, I will do the same thing.
Click on the standard deviation cell, enter an "=", then
click on the descriptive statistics model and then the cell
representing the population standard deviation.
For the probability, I enter 90%, which puts
5% in the lower tail and 5% in the upper tail.
Remember to set "plot which condition" to C to graph the results.
Also, make sure that this 90% is
centered, by entering a C in this cell.
Be careful to check this switch
and set it correctly, otherwise, your results will be wrong.
The model calculates that the score for a D is
76 and for an A is 99.
Now, that wasn't too bad, was it?
And I am also trying to show you that sometimes it is beneficial
to use other worksheet models to solve a particular problem.
Okay, we worked a lot of examples to illustrate how to solve problems
about continuous normal probability distributions.
Make sure to try problems on your own.
This is a chapter that can be challenging when using tables
in the back of the textbook that uses z-values.
One Excel feature I have used in this model is to make sure
that only specific entries are allowed in cells.
I do this in two places, in the "plot which condition" cell,
where I permit only letters A through G via a dropdown, and in the "Center,
Left, Right" cell, where only C, L,
and R are allowed via a dropdown.
You might think that this involves Visual Basic,
or VBA coding, but it does not.
I do this by using "Data Validation." Click on this cell,
then click on the Data ribbon, then Data Validation.
You will see that I am using a "list" to display only certain letters.
The list can be anything, even words, and even contents within a worksheet's cells.
Once you understand this feature, give it a try someday in your own
Excel models.
Another thing that I should mention is that Excel does
not have any tool to create a normal distribution chart.
It took me a while to figure out how to do this chart.
It took some intermediate data, which you can see if you scroll to the right.
It also took some creative thinking,
something that you might not have if you are new to Excel.
Many instructors would say that you have to use specialized
statistical software to create charts like this,
but I have found that if you know Excel, there are many things that you can do that
the average statistician would say, "Excel can't do that!" I will leave it
to you to explore how I created this normal distribution chart.
You will see this chart being used for our following few topics.
Remember that my models are completely open
so that you can explore how I have created this model.
(instrumental music)
Thanks for taking time to spend some moments with me.
I hope that you have enjoyed what I have shared with you.
If you ever have any questions or need my help,
please let me know.