Welcome to Highline BI348, class video number 69.
If you want to download this workbook BI348 chapter
11 video 69 and follow along, click on the link
below the video.
Hey, this is our last video in our simulation chapter, Chapter
11.
And it's our last video for this BI348 class.
And we have a great example.
We have a baseball World Series example
for how to use simulation.
Now the A's and the San Francisco Giants
are going to be playing.
And our goal is to calculate the probability
that the Oakland A's will win the baseball World Series.
And calculate the average number of games
that it will take to win the series.
Now, here are some of our variables up here.
Trials in that seven World Series games
will define a success as Oakland A's win one game in one try.
Probability that A's win one game, well it
changes each time.
Look at this.
0.65, 0.52, 0.475, 0.48 so we can't
use a binomial experiment.
Now these probabilities, where do they come from?
Well, if it's the Oakland A's, you
know Billy Beane and his statistics pals
put a lot of time and effort into calculating
each one of these things.
Like starting pitchers, match-ups, hitting,
on base percentage, home field advantage
and many other variables.
But each one is different.
Our random uncertain variable will be a binary variable x.
It'll be just that the A's win a given game.
Number of successes to win the baseball World Series?
Well you need a total of four.
All right I'm going to calculate the compliment here, even
though we really don't need it.
That's the probability that the Giants
will win any one of these particular games.
And now here's x1 to x7.
So we're going to have seven different variables
in a random zero or one for each one of these games.
Zero means they didn't- the A's didn't win.
One means they win.
So we're going to use equals the if function
and we've used the Rand function many times in chapter 11
here for simulation.
The Rand is an argumentless function
that will calculate a number between zero and one.
And I'm going to ask the question, hey
random probability of winning, are you
greater than the probability that we think the A's will win?
If it's greater than that, then we'll
say, comma for value if true, zero.
That means you- The A's did not win.
Otherwise one, they won.
Close parentheses.
Control-Enter, double click and send that down.
Now if I hit the f9 key to randomize,
you can see we get a different set of zeros and ones.
A's lose or A's win.
Now I need to calculate the cumulative total.
So down here it should be one, when
we get down here it should be two,
and finally it should be three.
So I use equals the sum function.
And I need to say E17-colon-close parenthesis.
We need an expandable range here.
So I'm going to put my cursor and touch
the first e17, f4 to lock it.
That expandable range, Control-Enter and copy it down.
Any particular cell, you can see, sure enough,
the sum function is expanding and adding
the total games that the A's have won cumulatively.
Now if I hit f9 you can see, there it is.
Four wins mean they won the World Series in game five.
Now we want to come down here and think about a simulation
formula because again we have to answer both of these questions.
And I'm going to get a little trickier.
I'm just going to look up four within this range right
here using the Match function.
Equals Match is a lookup function.
The lookup value will be- I'm looking up
four, comma within this look up array, right here, comma.
And we need to use exact match.
Now the advantage and the beauty of exact match
for this example, is that if there are duplicates over here,
because sometimes our formula will show 4, 4, 4,
it will only pick up the relative position
of the first number four.
So again, match will look through a range
and tell you the relative position
of the item it's looking up.
That means the number four.
So when I Control-Enter right now, look at that.
Match found 1, 2, 3, 4, 5.
So the A's won in the fifth game of the World Series.
And the Match function is never going
to report the wrong answer because it's only
going to find the first number four because we
have exact match.
Now when we hit f9, there will be
some cases like this one right here where
match doesn't find a four.
That N/A is going to mean they didn't win the World Series.
Now we want to copy this formula down.
We have Control-Down Arrow, we have our 10,000 simulation
numbers there that we're going to need for our formula
or are just sitting there to trick the data table.
Because we're going to copy this formula down.
Highlight, Control-Shift-Down Arrow, Control-Backspace.
And now to copy this down we go to Data, Data Tools,
What if Analysis, Data Table or we can use the keyboard Alt-D-T
for data table.
We do not have a row input.
We have a column input.
And we're going to trick the data table feature
by clicking an empty cell.
Now because this empty cell is not connected to that formula
right there, data table will try to substitute
each one of these numbers in.
But because that formula is not connected, it just won't work.
All data table will do is copy our simulation formula down.
And when I click OK, instantly.
Now as we saw in earlier videos, we
use the data table feature because in general it
will calculate faster than a formula with lots
of randomizing variables in it.
Now look at this.
In our first simulation the A's won it five times.
And in the second simulation, they won six times.
Four times, didn't win, didn't win, didn't win, and so on.
Now how are we going to deal with N/A's?
Up here, because we have to calculate probability
that the Oakland A's win the World Series, and then
average number of games it took.
Well let's first do the probability that
the A's- Oakland A's win the baseball World Series.
I'm going to actually use the Count function.
Now the Count function is programmed
to only count numbers and ignore everything else.
So I'm going to click on the top cell, Control-Shift-Down Arrow,
Control-Backspace to jump back to the active cell.
Close parentheses.
And I didn't count how many simulation values I have.
So I'm going to divide by and I'm going to use rows.
Rows tells me in a range, how many rows there are.
Control-Shift-Down Arrow, Control-Backspace, close
parentheses.
And that formula will calculate the probability
based on this series of individual probabilities
for winning games, that the Oakland
A's will win the World Series.
If I hit F9, you can see it's changing each time.
Now the next calculation is the average number
of games it will take to win the World Series.
Well because we did our match formula and copied it down,
we can just take an average of all these.
However the errors will mess up a straight average function.
So instead of using average like we've
seen two times already in this Chapter 11,
we'll use the Aggregate function.
And we're going to use the first argument, number one,
to tell it please do the average calculation, comma,
and unlike the other two times in this chapter when we used
aggregate, we chose ignore nothing,
but here we want to choose ignore error values.
So this is a way of using aggregate
to calculate number one function, which is average,
by ignoring errors, number 6 for options,
comma and then the reference.
Click in the top cell, Control-Shift-Down Arrow,
Control-Backspace, close parentheses, and Enter.
Now if I hit f9, f9, f9 Now we have our two calculations
based on individual probabilities
that the Oakland A's will win a particular game
against the Giants.
We've calculated the probability that the Oakland A's will win,
and the average number of games.
Wow.
That was a lot for Chapter 11.
A lot of amazing tricks for simulation.
We saw a bunch of different randomizing formulas
for different variables that were
based on particular probability distributions.
And we saw that each time we use the data table
feature in an empty cell to create
our many simulated values.
All right that's it for BI348 class.
We'll see you next video series and video class.