(instrumental music)
Hello. I am Professor Rick Jerz
and I welcome you to this session titled
Discrete Probability Distributions Using Excel.
This session focuses on using my Excel model
for solving binomial probability distribution problems.
By using Excel,
solving these problems become quick and easy.
You will also be exposed
to more of my Excel modeling techniques.
Incidentally, Excel's Data Analysis ToolPak
doesn't help us at all with these kinds of problems,
so we are left to building our own models with Excel,
which is what I prefer.
I think you are going to enjoy this Excel model,
and begin to appreciate some Excel modeling techniques.
Let's get started.
Here is my Excel model for the binomial distribution problems.
To the left is an area where you enter some data.
Remember, your entries go into the green cells.
Don't touch the pink cells.
These are where the model calculates results.
The first cell is where you enter pi, the probability of success for each trial.
Below that, you enter the total number of trials, and then below that,
I give you two cells, one for the lowest number of successes of interest,
and the second one for the highest number of successes of interest.
When I was designing this model, I wanted to create a model
that could calculate both single and cumulative binomial probabilities,
so this is why I have both a "lowest" and "highest" number of successes.
If your problem asks for only a single trial,
enter that value in both the "lowest" and "highest" cells.
You'll see me do this when I show you how to solve some problems.
After entering the values for probability
and trials, the model calculates the probability of success.
Wow, that's it!
You don't have to worry about
memorizing any equations or doing the calculations yourself.
This is the power of using Excel.
Let Excel do all the work, and you can sit back and interpret the results.
You will find yourself quickly solving binomial
distribution probability questions with a bit of practice.
To the right of the data entry area, I provide a chart showing you
the binomial probability distribution for the data you entered.
The bars represent the number of trials.
In this case, there are six bars because the binomial distribution
has a condition for "no success," meaning zero.
The red bar, or bars, represent the successes of interest.
In this case, we are interested in exactly three successes, so its bar is in red.
The precise height of the bar is sometimes hard to pinpoint,
but the calculation to the left is exact, .3125.
When the question involves the cumulative binomial distribution,
meaning more than one value for the number of successes,
you will see more than one red bar in the chart.
Remember that there is a family of binomial probability distributions,
so the number of bars and their heights will automatically adjust
based upon your input values.
You will see this happen when you solve problems.
Wow, isn't this great?
I give you both precise,
calculated results, and a graph to help you understand the scenario.
This is more than what the author provides in the textbook.
You will see me provide both "calculations" and "graphics"
in most of my future Excel models.
Recall that most textbook authors provide a series of binomial distribution tables
in the back of the book for different binomial distribution "families."
In my model, I somewhat replicate these tables below my graphic.
However, my table is dynamic; it changes
based upon your input probability and the number of successes.
This becomes much better than any book that contains
many pages for binomial probability distributions.
My table uses the "highest # successes" to generate this table,
and the probability is the crossing of the two yellow highlighted cells.
I provide this table much more as a reference.
I don't use it to calculate probability because my model calculates it.
The yellow highlighting of input values helps
you read the table, which textbook tables cannot do.
So, you do not need the chart or the table.
You just need the area where I do the calculations.
Let's use this model to solve some textbook problems.
Recall this problem.
There are five flights daily from Pittsburgh via US Airways
into Bradford, Pennsylvania Regional Airport.
Suppose the probability that any flight arrives late is .2 or 20%.
What is the probability that none of the flights are late today?
The author uses the equation to solve this problem.
In my Excel model, I enter .2 for the probability of success.
The total number of flights, or trials, is five.
And we're interested in exactly zero occurrences.
This is not a cumulative probability question
because the question asks for "exactly zero."
So in my model, I have to enter zero
for both the highest number of successes and the lowest number of successes.
Excel calculates .3277,
which is exactly the probability that the author calculates.
Look at the chart for this scenario.
We see a red bar representing the "zero" number of successes.
Although it can be hard to interpret this bar's height precisely,
visually, you can see that it is about .325.
If you wish, you can verify this value by looking at the table below.
Well, this was pretty easy, wasn't it?
It is certainly easier to do than using the equation.
Let's try a little more challenging problem from the textbook.
5% of the worm gears produced by an automatic,
high-speed Carter-Bell milling machine are defective.
What is the probability that out of six
gears selected randomly, none will be defective?
In my Excel model, I enter .05 for the probability of success.
And we're interested in exactly zero defective
gears, or zero successes.
Again, this question is for exactly zero.
It is not a cumulative probability question.
So in my model, I have to enter zero for both
the highest number of successes and the lowest number of successes.
Excel calculates .735,
which is the probability that the author calculates.
If you'd like to, you could look at the graph again
and see this value represented by the red bar.
For us, it is a simple matter of stepping through each of the textbook's questions
for exactly 1, 2, 3, 4, and 5.
Using Excel, it is as fast as changing these values.
Hey, bring on the questions!
This last problem illustrates a cumulative binomial distribution question.
The problem reads as follows: A study by the Illinois Department
of Transportation concluded that 76.2% of front-seat occupants used seat belts.
A sample of 12 vehicles is selected.
What is the probability the front seat occupants
in at least 7 of the 12 vehicles are wearing seat belts?
Be careful interpreting the question.
"At least 7" means to include seven.
In other words, we want the binomial probabilities
for exactly 7, and 8, and 9, 10, 11, and 12.
And we want to add them together.
In our Excel model, enter .762 as the probability of success.
Enter 12 as the number of trials.
Then enter 7 as the lowest number of successes and 12 as the highest.
Excel calculates the cumulative binomial probability as .9563,
essentially the same as the author's .9562.
The slight difference is that Excel is more precise
than using tables in the back of the textbook.
In my model, I am displaying the probability results to four decimals.
Internally, Excel keeps more digits.
If you want to see more or even less, you can change how many decimals
you view using these "increase" and "decrease" decimals tools.
If you want to see the results
as a percent, use this quick "percent style" tool.
Looking at the chart to the right,
you will see the bars representing 7 through 12 in red.
It would take a little time to add these bar heights visually,
so having Excel do the exact calculations is better.
But the chart does show you the red bars of interest,
which can be helpful to make sure that you entered the data correctly.
I should mention that these values, 7 and 12, could be any values.
If the question asked, at least 4 and less than or
equal to 8, we would use 4 and 8 in the model.
Well, what we are seeing is that Excel makes
these probability questions relatively easy to solve.
This is why I like using Excel for statistics.
A good Excel model is more powerful
and insightful than calculators or reading tables.
Excel is a much more modern approach!
I should mention that developing these models is not easy.
First, one needs to explore Excel's functions, picking the ones that seem
appropriate, and practice using it so that you know what the function does.
In this model, I am using Excel's BINOMIAL() function.
Excel does have some newer versions of binomial functions, BINOM.DIST()
and BINOM.DIST.RANGE(), but because I want to make
my models compatible with older Excel versions,
I continue to use the BINOMIAL() function, which is backward compatible
to earlier Excel versions and the current Excel365 version.
The BINOMIAL() function has four arguments:
the probability, the number of trials, the number of successes,
and a 0 or 1, where 1 represents "exactly equal to," and 1
represents, "from zero up to." For how I built this model, I use the 1.
As a side note, if your version of Excel does
not show the BINOMIAL(), or if I were to revise my model
with the current binomial function, I would use =BINOM.DIST.RANGE(),
as in =BINOM.DIST.RANGE(C12,C8,C13,C14),
where C13 is the lowest
number of successes, and C14 is the highest number of successes.
When you see Excel functions that use a period in them,
this is a clue that it is a newer Excel function.
As you can see,
keeping track of functions and their use can be a little challenging.
And when you create Excel models, you have to think about your users
and which versions of Excel you want to model to work.
If you click on the cell that returns the answer,
you might notice that it looks pretty complex.
Buried in this equation is the BINOMIAL() function.
The rest you see going on is my way of making the calculation
a little smarter, looking for possible data entry errors.
My equation begins with that great IF() function.
Then, you have to think about your own needs
and conceive how you want your model to work.
I decided to do calculations on the left,
provide a chart to the right, and a table below.
Finally, you build the model.
This is what takes time.
Understanding how to use the BINOMIAL() function might take 15 minutes.
However, building a model that uses this function can take 8 to 12 hours.
Also, as you build Excel models, you will see that
there will always be opportunities for improvement.
For example, initially, I didn't have a chart.
But then, after some work, I created it.
Let me show you a few additional Excel features that I have used.
You might wonder how I highlight values in the table, with yellow.
To do this, I use Excel's powerful "conditional formatting."
If you want to explore this, on the Home ribbon, click
on Conditional Formatting, and Manage Rules.
Another Excel feature I use is "Data Validation."
Try changing the probability of Success
to a non-decimal value, like 5.5.
When you try this, my model shows a dialog box "Data
Entry Error" and a note that the value must be between 0 and 1.
You can create "Data validation" rules from the Data ribbon,
then "Data Validation," and "Data Validation."
There are three tabs, one for "Settings," one for "Error Message,"
and one for "Output Message." Data Validation is a nice feature
to learn to use when you want to make your Excel models "smarter"
by preventing inappropriate values being entered by users.
You are welcome to explore my models and learn from them.
My models are not protected.
I want them to be educational.
So have fun exploring how I do things.
(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.