(upbeat music)
Hello, I am Professor Rick Jerz,
and I welcome you to this session
titled "Probability Using Excel."
In this session, we will explore ways of doing
both probability and counting calculations,
the two major themes within this topic,
of course, using Excel.
For the general probability concepts,
the rules of addition and multiplication,
you will not see much modeling because Excel is designed
to inherently add and multiply numbers.
However, for the topic of counting, Excel is very helpful,
and I will provide you an Excel model that can help.
I think you are going to find that my Excel model
makes counting calculations almost enjoyable.
Let's get started.
Here is the Excel model that I have provided you for probability.
It is not an elaborate model, but it does the job.
Although I have titled this model
"Probability," it only helps us with the chapter's
second section focusing on arrangements, permutations, and combinations.
For example, this problem that we saw in the previous session involves
calculating the probability of selecting two white shirts from a closet.
I can do this calculation in any cell
in any worksheet by just entering the equation, like so.
The calculations for arrangements, permutations,
and combinations are a bit harder.
This is where my model can help.
This upper section allows us to calculate the number of "arrangements",
given some components that comprise our system.
Recall that if we know how many "components" a system
has and the number of options for each component,
we multiply these values to calculate the total number of arrangements.
Yes, I realize that some systems might have many more than five
"components." I created this part to illustrate a general modeling method.
You can extend this model to suit your own needs.
Let's see how we would use my model to solve this problem that we saw earlier:
three car models and two wheel cover selections.
I type the three here, then enter the two.
These other values are unnecessary, so I left-mouse drag
through them to highlight them and hit the Delete key.
The model calculates six for the total number of arrangements.
To illustrate the tossing of two dice example, I can enter 6 and another 6.
The model calculates 36 arrangements.
This model in the worksheet's lower half calculates
permutations, combinations, and arrangements with repetitions.
We calculate arrangements with repetitions as n raised to the r power.
An example is having 5 different
paint colors and wanting to paint a room with four walls.
How many different ways can the room be painted?
I enter 5 as the number of colors, 4 for four walls,
and we see 625 ways, or arrangements.
I could do this simple calculation above, entering the 5 into four cells.
Yep, same answer, 625.
Another calculation that this model provides is n-factorial.
This calculation is for all n items being used, but without repetitions.
For the data showing right now, it is 5 times
4 times 3 times 2 times 1, which is 120.
This calculation represents painting a room with 5 colors and 5 walls,
but a color cannot be used on more than one wall, meaning no repetitions.
n-factorial represents a special case of "permutations."
Next, my model calculates Permutations and Combinations.
In Excel, you can quickly and easily view all cell equations
by holding down your control key along with the Grave key.
For Mac users, do not use the command key; use your control key.
This shortcut keystroke exposes all cell equations.
Another way to expose equations is by using the Formulas ribbon,
then "Show Formulas." On my Mac, I have to do it this way
because another more important application is using the control plus Grave key.
With formulas exposed, I can see my equation for "arrangements
with repetitions" as n raised to the r power.
Notice, for n factorial, Excel has a function
called FACT() for the factorial calculations.
And for permutations and combinations, Excel has
a PERMUTATION() function and a COMBINATION() function.
Wow, this is pretty nice.
It's much easier than remembering the equations,
and we don't even have to mess around with factorial calculations.
In our previous session, we saw this problem
of picking a team of five players from 12 total players.
How many different teams, or groups, can be formed?
In this case, the five team members are all equivalent,
so this is a combination since the sub-selection order is not important.
I enter 12 as the number of items and 5 as the sub-selection.
Very quickly, Excel calculates 792 different teams.
If the team has specific positions for these five players,
such as forward, guard, center, etc.,
this would be a permuation because order is important.
We see that there are over 95 thousand unique groups for this permutation.
Try using my model to solve some textbook problems.
Before I leave you, I'd like to show
you just a few Excel concepts applied in this model.
I have already shown you a few new functions
for permutations, combinations, and n factorial.
Above, where my model is doing the arrangements calculations,
I use an interesting new function that very few people
know about, called the PRODUCT() function.
It is almost identical to the SUM() function,
except the PRODUCT() function multiplies the cell values instead of adding them.
Pretty cool!
Another thing that you might notice in this model
is that Excel's light-grey gridlines are missing.
Sometimes you will see me do this
to make my models look a little cleaner to the user.
I do this by clicking on the View Ribbon, Show, and the Gridlines option.
You can turn gridlines on and off.
Also, you can turn the headings on or off.
You might notice that I have provided a description ahead of each section.
To make this wording more aesthetically pleasing, I do two things.
I formatted this cell with Word Wrap by right-clicking the cell,
selecting Format Cells, then the Alignment tab, and Word Wrap.
Word Wrap will try to wrap the words in a cell to fit the size of the cell.
I also adjust the row height by clicking and downward
dragging the border between the row numbers.
I think this looks pretty good.
One more thing that I did that is not obvious until someone prints
this worksheet is that I formatted this page to print on one page.
I did this using two techniques.
First, I selected the contents I wanted to print by left-dragging
through the cells.
Then go to the Page Layout Ribbon, select Print
Area, and Set Print Area.
Excel now knows the exact cells to print.
The second thing I do is a little more of an advanced feature.
Click on Page Setup, then Page, and Fit to 1 page wide by 1 page tall.
Click OK.
This is an important Excel printing technique that you should learn
whenever you want your models to print exactly on one page.
I encourage you to explore the other options available in
this Page Setup dialogue box.
(upbeat 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.