Learn how probability, math, and statistics can be used to help baseball, football and basketball teams improve, player and lineup selection as well as in game strategy.

Loading...

Learn how probability, math, and statistics can be used to help baseball, football and basketball teams improve, player and lineup selection as well as in game strategy.

Statistics, Analytics, Microsoft Excel, Probability

4.5 (45 notes)

- 5 stars34 ratings
- 4 stars5 ratings
- 3 stars4 ratings
- 1 star2 ratings

À partir de la leçon

Module 3

You will learn how Monte Carlo simulation works and how it can be used to evaluate a baseball team’s offense and the famous DEFLATEGATE controversy.

#### Professor Wayne Winston

Visiting Professor

Okay, and let's do one more illustration of Monte Carlo

simulation to teach you a new Excel function that can help you in simulation.

It's the infamous or the famous birthday problem.

So let's suppose you have 22 people in a room.

What is the chance at least two of them have the same birthday?

You could ask about more than two of them.

Okay and the answer is roughly 50, 50 which surprises people because they think

you need about 180 people in the room since that's half of 365.

So assume no February 29th birthdays, to keep it simple.

So how would you model this problem?

Well, you would generate 22 people's birthdays,

okay, and the function for that, there's a function we haven't learned about.

Rand between n1 and

n2, Is equally likely to be any

integer between those two of n1 and n2 are integers.

So for example, if I would do, rand between one and 365,

I'm equally likely to get any number between one and 365.

And that will generate the people's birthdays.

And so when I generate the people's birthdays, the 22 people,

then I can count how many people have each day of the year's birthdays.

One through 365, and if any day has at least two, then I've won the game.

That's one way to do this.

Okay.

So, let's look at persons one through 22.

And I'll just drag it down.

It's simple enough.

And I'll generate the birthdays.

So, we'll use rand between one and

365 So a one would be January first, etc.

So I've got 22 people's birthdays the question are any two of these the same?

Well, I could do every day of the year, one through 365 and

count how many of those people have a birthday on each day.

So I could do one, the key was using that rand between here.

So I can do one, I can do home, build series.

One through 365.

Columns.

Okay, now I'm going to do countif.

Let's name this range.

We can call it Birthdays.

Put in the name box.

So, I do countif, then I could use F3, birthdays,

and then I could see how many times they have birthday,

first day of the year, which is January 1st.

Now, as a check, you should add up to 22.

I should get 22 different birthdays here.

And I do.

If I hit F9 to recalculate, I always get 22.

So when do two people have the same birthday?

So I don't know, but, see, if the max of this column is greater or equal to two,

then they have the same birthday, so I'd say if the max of this column,

is greater or equal to two,

I will give myself a one and otherwise a zero, and that way, I win the game.

See there, two don't have the same birthday.

There two have the same birthday.

What is that?

I mean I could highlight the column.

It's a conditional format and that might help.

Remember, Home Conditional Formatting.

Home is where the conditional formatting is.

So top and bottom roles, I could say.

Highlight the top one.

Okay, actually a better way to do that would be anything greater equals two.

Home conditional formatting, lets clear the rules from the selected cells.

And now do Conditional Formatting,

Highlight cells where anything greater than one is what I want highlighted.

Okay, so now here, there should be one greater than one.

Where is it?

There it is.

The 201st day of the year.

Okay, two people have that birthday.

Now I can fun this 5000 times,

be a bit slow, but I'll go one, home,

build series, columns, put 5000,

and then this will be my output cell,

data, what if analysis, theta table.

No row input cell again.

Column input cell And it's filling,

but this is going to be slow because I had to generate, okay, 22 people's birthdays.

So the question is, average of this column?

So the chance of a birthday match,

if you want to all it would be the average of this column.

And I think it should be about 50, 50.

So the Ctrl + shift down arrow.

I get 48% there.

If I hit F9 you gotta let them recalculate, it's going to be slow.

I get 48%, 47%.

So, it's a little bit less than 50, 50.

Maybe you'd need 23 people to make it over 50, 50.

You can add another person there.

But we're getting about 46, 47% chance.

So it's about 50, 50.

If I do that one more time, well it slowed it.

48% chance, 48% chance.

But it's close to 50, 50.

Okay so again, we simulated 22 people's birthdays.

We found out how many people each day of the year had two birthdays or

more at the max.

If there was a day in which these two people have the same birthday we sort

of pointed that they.

So there's 47% one more time.

We have to let this data table one finish here.

I get 46%.

So that's how the birthday problem can be simulated,

could be solved exactly, but I don't think we're going to spend the time to do that.

Coursera propose un accès universel à la meilleure formation au monde,
en partenariat avec des universités et des organisations du plus haut niveau, pour proposer des cours en ligne.