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

来自 休斯敦大学系统 的课程

Math behind Moneyball

34 评分

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 some very important Excel functions that

let you do a lot of important calculations that are needed in Sports Analytics.

The COUNTIF and COUNTIFS function, so these do conditional counts.

And so, we're going to deal with the data in NBA 2015 file.

Okay, so we've got, courtesy of basketball reference again,

I can't say enough great things about this site.

We've got basically, data on NBA players in the 2014-2015 regular season.

So we've got their teams here and so let's suppose the start here, we simply want

to figure out how many players were listed on each team during the season.

Okay, well first of all we need a list of teams.

And so we'll teach you an Excel trick for that.

We have all these abbreviations.

There's something called Remove Duplicates under Data tab that lets you find

unique entries in a column or set of columns.

So if I would copy this team stuff here to another worksheet.

And then select all this,

go Data > Remove Duplicates on the team.

Okay, so there are 32 teams there.

Well, there's actually, there we go,

there we've got our teams there and we'll alphabetize those.

It's got the word team there, so there are some heading, some totals and team.

There's actually 30 NBA teams, but basically, if I copy this stuff here.

And then I'll alphabetized to get a team list and by the way,

Chapter 19 of the Excel book has the COUNTIF and COUNTIFS functions.

So if I would copy what we've got here, Ctrl+C,

and then Right-click, drop, paste, whatever.

Okay, so now, let me put this alphabetical into teams.

So I can go Data > Sort.

My data has headers A to Z.

Okay, so I've got Atlanta, Boston.

Now somewhere there are things that aren't teams.

So this is not a team.

And totals is not a team.

Okay, so I should really.

Don't want to delete, because I would delete a player there, but

basically, I'll just type Toronto, Utah and Washington.

Okay, now row seven, I've got all the teams.

So how many players?

Now range names will be convenient here.

I'm going to name each column with its heading, so then I can use the functions

wizard to basically fill in the arguments of a COUNTIF function.

So COUNTIF, you give me a range and you give me a criteria.

And then we're not going to do everything on criterias.

The Excel book, yeah, it has a lot on this.

But let's name each column with it's heading so I Ctrl+Shift+right arrow,

Ctrl+Shift+down arrow, Formulas > Create From Selection > Names In Top Row.

Okay, so, like if I've got field goal percentage,

okay, it would go to this column.

Okay, free throw percentage, I'm going to be using later, is this column.

And right now, I'll be using the Team.

T-m for Team.

Is this column.

Okay, so what I want to do is count how many players on each team.

So I can go to the function wizard, and I can find this function

under All Functions, and I can start typing fast, COUNTIF.

Let's try again.

There we go.

So I'd say, COUNTIF, and I would do F3, the Range is the team.

And I'd render it like it's dollar sign.

The criteria is simply the team name.

So what that says if the team is Atlanta, count how many times that happens.

In other words, how many times do you see Atlanta abbreviation in the team column?

The answer is 16.

I can Double-click and copy that down.

And I now know how many players are playing for each team.

Okay, now if I want to copy this to another worksheet,

I could do Right-click, move or copy, there's other ways to do it, but

this is easier to show you, to this worksheet, create a copy, okay.

So let's say the 50% field goal percentage,

80% free throw, and let's say 40% three point court.

Let's count how many players shot 50% on two pointers.

We've got that right here.

Two point percentage.

And we've got three point percentage.

Effective field goal percentage, we'll talk about later.

Right there.

A lot of guys didn't try three pointers.

So if I select this whole column,

that Ctrl+Shift+Down arrow fails when you have a blank.

And then free throw percentage.

Okay, so I can put a screen in here like we talked about in our last video.

So I can say three point percent.

And let's say that's 40.

Two point percent, 50.

And let's say free throw percent, say .40

0.50 And 0.80.

So how many players did this?

And you could use the conditional formatting to highlight them.

Okay, so this involves COUNTIFS.

Okay, which involves multiple criteria.

So I could say, COUNTIFS, and then the syntax goes like this.

So basically you give the first.

Criteria and then for range one.

Okay, so the first criteria.

We'd go range one is the three point percent.

So we'd go three point.

Use the F3 trick.

Okay, see, it put an underscore in there, which I didn't realize.

So it's the three point percent and then I would say greater and equal to.

You need text here.

So you say greater and equal to.

And then you point to this cell.

And, then the next range, let's say would be two point percent.

And again, you put this greater and equal to.

And now you need the & sign, this is the concatenate operation in Excel,

which combines things, you need to combine that with the fifty percent shooting.

And finally, we've got three point shooting.

Sorry, we've got free throw shooting.

Free throw should be under F, I would guess, free throw percent.

And then I've got my friendly greater or equal to, again.

And I gotta concatenate it with this.

So how many of those players are there?

So there are nine players who did this.

So nine players.

Shot at least 50% from field.

Kyle Korver I know is one of them.

80% from free throws.

40% on three pointers.

Now if I would make that 90% three point shooting, there may not even be any.

Oh, there's four people who did that.

How about that?

Okay, that's pretty amazing.

Those are your really star shooters there.

Okay, so that's how the COUNTIF and COUNTIFS functions work.

So in the next video, we'll learn how you can use SUMIF and

SUMIFS functions to do conditional sums, rather than conditional counts.