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...

來自 University of Houston System 的課程

Math behind Moneyball

38 個評分

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.

從本節課中

Module 2

You will concentrate on learning important Excel tools including Range Names, Tables, Conditional Formatting, PivotTables, and the family of COUNTIFS, SUMIFS, and AVERAGEIFS functions. You will concentrate on learning important Excel tools including Range Names, Tables, Conditional Formatting, PivotTables, and the family of COUNTIFS, SUMIFS, and AVERAGEIFS functions.

- Professor Wayne WinstonVisiting Professor

Bauer College of Business

Okay in this video we're going to learn about the average hip and the average hip

S functions covered in chapter 20 of our data analysis and business modeling book.

Again our data is courtesy of baseketballreference.com.

And so these do conditional averages, they're really just like SUMIF.

And SUMIFS average, it does averages on a single criteria.

AVERAGEIFS does averages on multiple criteria.

So let's suppose you want to figure out the average

rebounds per player, based on the players on the team.

Okay. So what I could do for that.

And the function wizard makes this simple, so

if I go to function wizard and if I type fast, AVERAGEIFS.

I didn't type fast enough, but there it is.

It's already AVERAGEIF.

Okay, so the range in criteria, the range will be again the team.

And the team in this case, we want it to be Atlanta.

And the average range would be the rebounds, which I think is TRB.

And so the way you would translate would be for

everybody who's on the Atlanta Hawks, take their total rebounds and average them.

And so that's not rebounds per game.

I'm sorry that's average rebounds per player on the Hawks.

So the average Hawk here got 184 rebounds which is about 2 rebounds per game.

Okay.

So now let's try something a little bit more interesting.

Of all players who averaged at least five rebounds per game.

How many assists did they have?

Okay, so I added two columns here, rebounds per game and assists per game.

So I'll name those columns.

I'll go Formulas > Create From Selection > Top Row.

So I think I've got rebounds per game if I go to the name box.

Yeah, I've got that.

And I think I've got assists per game.

Yep, I got that.

So in other words,

let's say average assists per game if rebounds

per game is greater than five.

For each team.

So in other words, what I want to go in this cell would be all Atlanta

would average at least five rebounds per game.

How many assists per game did they average?

Probably not that many because people get at least five rebounds per game or

usually not.

Okay, so I can go to function wizard again, and

this will be average habisca with two criteria.

Rebounds per game must be at least five, and

the team must match the team with more on an average basis.

You can have up to 256 criteria, I would hope that that's enough.

Okay. So I would go name, manager.

Sorry.

I'd go to function wizard.

Let's see how fast I can type.

So the range I want to average.

Okay, that's assists per game.

The first criteria is the team, so I hit F3, and the team will be.

Let's match Atlanta.

And the second criteria would be rebounds per game.

Greater and equal to five.

Everybody averages about five rebounds per game.

How many assists per game did they have?

Okay. 2.6 per player, which isn't that high.

Now, all the players who have at least five rebounds per game are the Celtics.

Now I don't know who any of those guys were.

But they averaged a fair number of assists per game.

Maybe it's a guy who didn't play very many games.

I don't really know.

Okay.

But you can see these numbers are fairly low.

But what this does it says, okay, again you look at that formula.

Oops.

Okay.

You'd average the assists per game, the team is Atlanta,

rebounds per game is greater or equal to five, okay.

And that's

you'd average at least five rebounds per game that's how many assists per games.

But they assist per game that they average.

Okay, so we'll give you a homework problem on these great functions that you

can use to study that you'll then have a test question on.

Okay, and then we'll talk about pivot tables,

which sort of is a more powerful way to do what these great functions do.

And very useful in sports apps.