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

36 个评分

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'll learn how to do conditional sums.

In other words, sum one column based on a condition that may involve one or

more of the other columns in our spreadsheet.

And this will use the SUMIF and the SUMIFS functions,

easiest to explain by example and probably best to use the Function Wizard

To input the formulas.

Okay, so we've got our basketball data again, let's use that.

And so let's figure out how many total points were scored by

each team based on the players.

So in other words, for Atlanta, what a great season this year, and

they're playing Cleveland in the conference finals as I tape this.

We would like to know whenever there's an Atlanta in the Team column,

basically add up the points column.

So if I go to the function wizard, this becomes pretty simple.

So go under all functions and I type some if.

I never get it.

Okay. So I just follow the syntax.

So range and criteria are just like they work for tenant, so

I want to get the team column out here.

They have three there.

So if the team column matches Atlanta,

then I want to add up, I guess, it's the points column.

Okay, and then I click OK there.

And I get the total points that they've scored.

Copy that down, okay.

So I have total points that were scored by all of the players on this list here.

And if I wanted to know the exact sets, for instance,

if the team equals Cleveland, they add up the points for the players on the list.

Now let's insert a column.

Let's say points Score.

Or let's say rebounds On that team by player.

Rebounds basically.

By players averaging at least ten points per game.

Something like that.

So I could do Ctrl+1, Alignment, Wrap Text.

Okay.

So, I want to say add up the rebounds column,

but first I want points per game, so we should insert a column here.

Insert a column, points per game.

So, I could take total points And

divide by [INAUDIBLE]

Okay. Yeah, I want to reformat that because that

just looks darn ugly.

And I get error messages, I could use to get rid of those.

But if I'd say number, double digits.

So I want to add up the rebounds, so I'll go to the function wizard.

Sum fs.

Okay. Just follow the prompts there.

So the sum range I want to add up would be the rebounds.

So I'm giving that three.

See the range name mixed in so simple.

Total rebounds.

And then the first criteria would be the teen, so I hit F3.

Say the team is equal to what?

Games Atlanta.

And then averaging.

Okay, now I didn't name this column but that's okay.

So I do points per game.

And I need to dollar sign that,

so you can see it would have been better to name that.

I could say greater or equal to ten.

Okay, so that says okay.

Add up the total rebounds of the team that's Atlanta, and

points per game is greater or equal to 10.

Okay.

And here are, let's say, for the Golden State Warrior team this year,

add up the team rebounds, if the team is equal to Golden State and the total

points per game is greater or equal to ten.

Okay, so some at best will work on multiple criteria.

[SOUND] [PHONE RINGING].

[SOUND] And SUMIF on single criteria.

And so in the next video we'll learn about average if and average fs.