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

35 评分

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, so we learned a lot about conditional formatting.

The top-bottom rules.

The highlight cells.

The data bars.

Color scales and icons, so that's very useful.

But sometimes Yyu want to put formats in cells

that don't really fall under these categories.

So here's an example.

So in the file Batters 2014, we've got data from band graphs on how hitters do.

So suppose every hitter hit over 20 home runs.

I want to highlight all their data.

So Mike Trout hit 36 home runs, highlight his data.

Andrew McCutchen, 25 home runs.

Highlight his data.

Ben Zobrist, 10 home runs.

Don't highlight his data.

So highlight all data

For hitters who hit greater or equal to 20 homeruns.

Okay.

Now to do this we need to know something about what's called real home conditional

formatting, new rule, the formula option.

Okay. But before we understand that we

have to understand logical formulas.

So like Mike Trout hit 20 home runs.

So for example, if I would type the formula equals

E2 greater and equal to 20.

I will get a true.

If I would copy that down I could double click the left mouse.

Here's a false goods, Alex Cordova did not hit his 20 home runs.

Okay, so every row where there's a true here,

I want to bathe it to highlight the entire row and that's what the formula does.

So, conditional formatting get it to go Conditional formatting,

use a formula, new rule, use a formula.

Okay, you start typing a formula.

Based on the upper-left hand corner of the range you select that formula

copies down and across and whenever that formula returns a true,

then the format you select will be placed into the cell.

Okay, so the key is going to be the dollar sign.

So again you know in poker they say know when to hold them, know when to fold them.

Again in Excel it's know when to dollar sign, know when not to.

Okay, so I'm going to start here with Mike Trout's.

Select control check right arrow, just over to here.

Control shift down arrow.

Okay, and I'm going to go home, conditional formatting.

New rule.

Use a formula.

OK. Now the dollar signs are the key.

I'm going to type equals.

OK. Basically, I want to check home runs.

So, I'm going to say, guess we've lost that there.

Sorry.

Additional formatting, new rule, use a formula, equals, scroll up, okay so

we're going to hit the home run.

So E2 okay that's greater or equal to 20 but

I've a dollar sign in the E and not the 2.

I'm using F4 where I can do that.

So you have to wear cycles the dollar sign.

Okay because I always want to look in column E and copy down and across.

So I want the 2 to change as I move down but

as I move across, I don't want the e to change.

So now it should check in every cell is what's in column E of that

row greater than or equal to 20.

And if it is, let's say yellow.

Okay, so everybody hit at least 20 home runs, their data's highlighted.

And if you know anything about Excel, you can sort on color.

Let's just call this column Logical.

So what I could do is I could go Data > Sort column that has headers.

And it doesn't matter which row, let's say the homerun.

And I could say Sort on Cell Color.

Put yellow on top.

And that's pretty cool.

So everybody hit 20 home runs on top, and everybody who didn't goes down there.

Okay, let's do something trickier.

The 20-20-20 pool.

Okay. So let me insert a few playing pros.

Okay.

So let's highlight the data for each hitter.

Who hit greater than or equal to 20 home runs, doubles,

and we'll do a 20, 20, let's call it the 20, 20,

10 club so we have some more people, and ten stolen bases.

So we have multiple criteria here we're filtering on, and

we want to highlight based on that.

Okay.

So what I can do is I can have the criteria be variable.

So I could put home runs, let's say 20.

Let's say doubles, let's say 20.

And let's say stolen bases which we have at least ten.

And now what I can do is I can do an AND statement.

So in other words, Mike Trout, I think would meet this criteria.

Well, we don't have doubles here, I'm sorry.

Okay, so just say home runs greater or equal to 20.

And we'll have greater or equal to ten stolen bases and hit at least

So I'll call this compound

Okay, so batting average at least 0.3.

Stolen bases at least ten.

Okay, so like, you can do an AND statement, you could also do ORs and NOT.

So like say AND, if this is greater or equal to 20.

And what stone base is is greater or equal to 10.

And batting average is at least 0.3.

Let's see we've got E5 greater equal to 20.

H5 greater equal to ten and not K5 sorry, batting average is M5.

Well, Mike Trout didn't can actually do that.

He didn't hit .300.

So there aren't going to be many people here who did this, I don't think.

Okay, there's a couple here.

Actually, Michael Brantley, and that's about it.

So I should make this a little bit easier so I could have more people.

Let's make it at least two.

So we've got E5 at least 20,

we've got E5 at least ten, and N5 at least two.

Sorry.

Okay, so we should point to those cells.

See I made a mistake there.

I can't vary the criteria.

So I'm actually okay with that because that's a common mistake.

I should say home runs great or equal to this cell that I've got a dollar sign.

And I'll say stolen bases greater or equal to this cell.

And I've got a dollar sign that.

And I'm going to have dollar sign the E and H here.

But the key is I also have to dollar sign my criteria.

So I think Mike Trout would fill the bill there.

And I hope there's a couple more.

Okay, there's a couple there, so that works.

And now the nice thing is this criteria is variable.

Okay, so now I can select this range,

And I can do conditional formatting.

Home tab, Conditional Formatting, New Rule, User Formula.

Okay, so I start typing equals, with an and sign, and there's got to be three

things that are true for me, to highlight a guy's statistics, it's all statistics.

I highlighted the whole range that I wanted formatted,

which is all the information on the players.

So if the home runs then you need to make that an E, greater and equal to 20.

And you refer to that cell.

And the stolen bases you need the dollar sign,

not the five but the H, are greater and equal to this.

And the batting average, which is here,

I've got a dollar sign for ten is greater or equal to that.

Let's check that I've got the right fill.

Okay, so I'm comparing E2,

F2 and G2 were going to be my criteria and their dollar sign.

And then columns E, F and

M I don't dollar sign the row, but I dollar sign the column.

And there we go, so all those people who basically met the screen of 20 home runs,

ten stolen bases, and a 280 average are right there.

Now suppose I said 30 home runs, 5 stolen bases and hit 250.

Okay, I'm going to get a different set of players there.

Okay, so I get Jose Batista, who only hit,

that the 250 doesn't seem to matter too much there.

Okay, that didn't erase, but the five stolen bases give me Jose Batista,

but if I go to ten stolen bases, Jose Batista doesn't meet the screen yet.

Okay, so I think this formatting is very useful to sort of

give you some visual cues when you're looking at sports data that may involve

hundreds of NBA players or Major League Baseball players.

Okay, so in the next video we'll learn some great functions for

doing some important calculations involving Conditional Counts,

Conditional Sum, Conditional Counts and

then we'll do Conditional Sums and then Conditional Average.