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

From the course by University of Houston System

Math behind Moneyball

25 ratings

University of Houston System

25 ratings

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.

From the lesson

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

Let's continue with our study of conditional formatting.

So how can we summarize numerical sports data and insert data in cells.

There are really three nice ways, visually, to summarize.

Data bars, and what that means is the bigger the number,

the bigger the data bar you put in a cell.

Color scales, big numbers would be green, small numbers red,

intermediate numbers yellow.

Used this a lot to summarize how good the lineups are for college and

pro basketball teams.

And then there's icon sets, like a thumbs up means a big number.

Okay, thumbs down means a, down arrow means bad number.

So let's go back to James Harden's stats let's look at his point in every game in

2014 and 2015 and summarize them with data bars color scales and icon sets.

And they all have virtues in this situation.

So if you want to summarize data bars you should widen the column so

you can really see the different between let's say a 6 point James Harding game and

a 50 point James Harding game.

So I widen this column and all you gotta do is select the data.

You teach this to a kid in kindergarten.

And you go home, conditional formatting, data bars, and

pick a color and there you go.

So when James Harden had a 6 point game there.

It's a really small data bar.

And you can see that the bars are big for a long time.

He's really red hot.

There's that 50 point game.

The biggest number will have a data bar that fills the entire cell.

And everything's proportional to that.

So I' think he's scored most was 51 points.

I can't find it here.

But see, that 50 point data bar fills almost the entire cell.

Now what do data bars do with negative numbers?

I should quickly talk about this, so with negative numbers,

if I had something like this, and I go Data Bars,

see, it makes the negative numbers point to the left and the positive to the right.

And so if you look at how many points you beat the opponent by,

you might have a negative amount if you lost the game, so that's fairly important.

Okay, now color scales you do sort of the same way.

You just select this.

You do home > you do conditional formatting > you do color scales.

I like green, yellow, red.

You can adjust the colors.

But, so when he really did well, you'll see a green.

The biggest number will be the greenest, and then they get less green,

then they shift to sort of red and orange and then the worst numbers are yellow.

Sorry the red numbers are the lowest the yellow in the middle.

So the worst game was 6 points that will be the reddest.

And 11 points will be slightly less red and etc.

But the games in the 40s and 50s will be the greenest.

But notice the 33 point greens start shifting to the other color in the middle.

Okay, now the icon sets are I think what most people prefer.

So what you do is you select the points.

And now there are a bunch of choices.

Conditional Formatting > Icon Sets.

I like the five grey arrows.

But it doesn't quite do what you think probably.

Most people are getting into cell phones bars of service.

So they like four bars to zero bars.

You can mix and match these as I'll show you.

Okay, so the really good games where he scored 51 points that's an up arrow.

But you probably think that with five icons,

the top 20% of games have an up arrow.

And you'd be wrong.

What happens here if you go to conditional formatting > managed rules,

by the way, clear rules will get rid of your rules, you can edit your rules.

And if you look here and you click on this, you'll see it says 80%.

So what that means is, okay, and 60%.

And if you look between the lowest James Harden game and

the highest James Harden game so he went from 6 to 51 points.

Okay, that's a 45 points difference.

Sorry, 6 to 51.

Okay, if you break that up into five equal areas, I guess you'd go

sort of like 43 to 51 I'm putting a quote here so it looks like text.

That would be the up arrow.

OK, now if I go 34 to 42 I might be off a little bit here.

You get the half up.

And then the next nine points, 25 to 33.

Half down sort of flat.

16 to 24 half down.

I'm off by a little bit here, because it should be 16 14.

And we derive that but roughly speaking total go in.

Now the problem with this is, okay,

it may not be in 20% of his games he scored 43 to 51 points, okay?

So, There we go.

And so you want 20% of the games to have an up arrow,

you need to change just the percentile.

So you select this range to the conditional formatting.

Like for example here, if you had a game in the low 40s.

I guess he did, but basically he scored 40 points it's a half up arrow.

But my guess is that should be a totally up arrow,

because that was one of the top 20% of his games.

So when I select here, for example it could be five or

one game between 43 and 51 you only see one up arrow.

And I don't think that's what you want.

So you'd go Conditional Formatting > Manage Rules.

Okay, change everything to percentile.

And by the way, you can change the colors here.

So instead of a dull grey, I can make that a green.

Instead of this down arrow, I mean, not stupid, but

I'll make that zero bars so you can just customize this.

So now that 6-point game has sort of cell phone bars.

And that 40-point game, if I can find it,

see now it's got an up arrow, so things changed.

One last thing on the icon sets.

Your managed rules.

Go edit rules.

Okay, reverse icon order.

So if you're looking at turnovers, a low number of turnovers would be good.

Not a high number.

So you'd want the thumbs up arrow for a low number.

You would click on reverse icon order.

But these things are very useful.

The data bars, the color scales, and the icon sets to summarize your data.

So what we're going to learn in the next video is how can you create additional

formatting based on f, rather than just click a button.

Clicking a button's fine, but it doesn't solve every problem which you'll see in

the next video where we look at some

Coursera provides universal access to the world’s best education,
partnering with top universities and organizations to offer courses online.