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

36 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 5

You will learn basic concepts involving random variables (specifically the normal random variable, expected value, variance and standard deviation.) You will learn how regression can be used to analyze what makes NFL teams win and decode the NFL QB rating system. You will also learn that momentum and the “hot hand” is mostly a myth. Finally, you will use Excel text functions and the concept of Expected Points per play to analyze the effectiveness of a football team’s play calling.

- Professor Wayne WinstonVisiting Professor

Bauer College of Business

Okay in this video we're going to show you how you can, and the next two videos,

how you can use the great play by play data on pro football reference to gain

a lot of insights on pro football.

So you go to pro-football-reference.com.

Okay, so there's something called play index.

Okay, now you go to Game Play Finder.

And what this is,

is a filter that will basically let you pick out any subset of plays you want.

You can filter around, there's just so many things, the Week Number, was it

a Pass, a Rush, was it a Field Goal, the Yard Line, the Quarter, the Years.

Now it'll only let you get 500 plays at a time.

So you have to sometimes make your filter pretty precise and

then just keep adding stuff.

So let's see how to get that on field goals in 2014.

So I can click field goals and I know there were more than 500.

So I might say from the opponents,

starting field position, 1 to the 15 yard line.

Okay, and that would give me every field goal not passes or rushes.

Okay, from the opponents 1 to the 15 yard mark.

Okay, line of scrimmage, so they'll come up right here.

All right, back in a minute.

Okay.

I see it didn't come up there.

Let's see what we get now.

Okay, let's see.

Oh, this should be opponents 15.

Sorry.

Okay, Okay, so

now I've got this information which I can just copy and paste,

I wish there was an easy way but i just copy it and paste it.

And then I would let me show you what we've got.

So we've got who was playing, who was the opponent at the time.

What I really care about is this column.

This tells you who kicked the field goal, how long the attempt was,

was it good or not.

And then interesting, the last column is points added, points added by the play.

So this was a blocked field goal run back.

That's a terrible play.

It would be minus 3.5 quarter points, okay?

And here I guess was a fake field goal.

Tony Romo passed the ball and they got a touchdown.

That was worth plus 3.9, okay.

Oh I think I've got here, I must not have clicked on,

yeah pass should not be selected I'm sorry.

Okay, but it's good to see the mistakes you can make.

Alright so here we got all these field goals, so

this field goal costs 3.5 points.

This field goal costs 3.39 points.

Here is a 31-yard field goal, that was good.

That added .49 points.

Okay, so then copy all this, put it in a worksheet.

I'll show you, and then we'll show you how to analyze that.

And then I would go, I want the 1 to 15.

I would go 16 to 30.

31-50 yard line nobody kicks a field goal I guess more than

the 50 yard line of scrimmage that's a 67 yarder.

So wow a field goal in 2014 has that data.

Okay and so what I did is put this together there's over 900 field goals.

Okay, now first I want to find out the length of the field goal.

And the key is working with column N, which is in my case,

the play-by-play data.

It appears we have to learn about text functions, these are very important,

they're in chapter 5 of the DAVM book, but you'll learn about them through example.

But if you analyze football play-by-play or probably if you have play-by-plays for

other sports, you really need to understand text functions.

Okay so I want to find the yard line.

So first, if I could find the word, what's the common thread in each of these rows?

If I could find the word yard and I could start one, two, three.

Three characters before that and take the next two, I'd be okay.

So let's find the word yard.

Well guess what there's a function called the find function.

So if I say find, okay, I want to find in text,

I put the quotes the word yard in this cell, comma one.

So the yard would be at the nineteenth character and

I copy that down, fifteenth character.

Okay.

Now the length of the field goal, and

then I can do all sorts of analysis like later to try and predict the chance of

making a field goal in the NFL based on the length of the field goal.

So now let's see there's a function called the mid-function.

And so if you go to Excel and you type in what should be the three arguments for

the mid-function all spelled from the middle of the cell.

The cell you pull from, Okay, that's the cell you pull from.

Where do you start and how many characters?

So if I want to find the length of a field goal,

I'd say mid-function, of course I'm pulling from this cell.

Okay, now where am I going to start?

I'm going to store it I believe where I found this minus three characters and

then take two.

Now if that doesn't work I'll adjust it.

See that worked.

Okay, now the problem is that's not a number.

Anything on the left side of the cell is not a number.

But you know there's a function for that.

Just add the word value.

And then you'll make it and up.

Okay.

So then I can find the length of the field goal.

Just copy that down.

And then I could use once I find out is it good or no good.

I can find out the average length of a good field goal,

the average length of a no good field goal.

I can figure out what was the field goal percentage on field goals that were at

least 40 yards?

Okay there's just lots of things I could do.

Okay so now the question is, was it good or no good?

Well this is a little trickier.

See if I see the word no good I know it was a no good field goal,

so how do I know I can find the word no good there?

I could use the Find function.

I could do an if ever find no good.

And if I can find no good it gives me a number.

Otherwise I'll put a plank in that cell.

And if there's a plank in its cell then it wasn't no good, and it was good, okay?

And then I could have a column that'd say was it good or no good.

So no good, I'll do an if error, okay?

And I want to basically find the word no good,

it may not be there, in this cell, starting in the first character.

Okay, otherwise I'll put a blank.

Okay, now when it's good.

Okay so those blanks mean it was good, okay.

So I'll call that no good with define function.

Okay, so now basically I can say good or no good.

Can control one alignment wrap text.

Okay.

So I can say, if this is greater than zero,

that means I could find the word no good, I could say no good.

Otherwise, I can say good.

But I'll say no good.

No good.

That's not quite working.

Let's say if the length of View Six is greater than zero.

So we'll use another function key.

So the length of View Six means there's a number there that's basically,

you can count how many characters in that.

So let's see if that works.

Okay, there it worked.

Okay.

So in other words, if there is a number in here that has

a character in there it says no good, but see this one was good,

this one was good, because it doesn't say no good, where's a no good here.

This one was no good.

This one was no good.

This one was good.

[SOUND] Okay.

So now I've got it, this one was no good.

So now I can get information.

Let's look at how many field goals of at least 40 yards.

And we can look at how many are good.

So really let's just name these three columns.

Formulas create from selection.

Then we say top row.

So I gotta name, good or no good, which is this column.

Gotta name length of field goal and I've got no good.

So I could count, you count if, okay.

And I could use f if the length of the field goal is greater or

equal to 40, how many of those were there?

428 field goals were attempted from 40 yards or more.

And was it good or not?

I could do count it best.

Okay, so if the length of the field goal, Was greater or

equal to 40, and I'm using F3 key here, the good or

no good column, Was it good?

That will tell me what fraction of those are good.

So the fraction that were

good would be this.

71% were field goals that were at least 40 yards were good and,

we get a lot of information out of that.

Now, I could also get the name of the kicker, and

then I could work on trying to figure this out for each kicker, because the name of

the kicker would be everything to the left of the second space.

Okay, so there's a left function, so, I'm assuming nobody has middle name.

So let's find the first space, just so show you more about text functions.

Let's find the second space, and

there's also obviously the write function, which sometimes we need.

But these text functions are so important, not just in Excel.

When I've taught some classes to sort of the staff at universities and

other companies, I find that knowledge of text functions can save them

from incredible amount of manual labor.

People will do something in five thousand rows manually when they can do

it automatically with a text function.

Okay so the first base I would say find

space in this cell, comma one.

Now if I want to find the second space, how do I do that?

I say find the space in this cell and

I start one after the first space.

Find a space.

Sorry, that should be N6.

So the second space would be the 15.

So once I find the second space, so how can I find the kicker's name?

Take everything from the left of the cell and

stop one before the second space.

See now I've taken that stuff from pro football reference that I can really

do a lot.

When we were in logistic progression, we could figure out how the length of

a field goal affects the chance of a field goal.

So I think what we've learned,

I mean we've learned a bunch of functions here but they're really important.

The combine function.

The LEN function.

And the left function and the mid function.

But just this one example, applying text functions to sports, has taught us a lot

of stuff that will be useful in your daily job, even if it's not in sports.

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