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

44 个评分

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 the next few videos,

we're going to teach you about conditional formatting in Excel, which can really

enhance your presentations in sports analytic's or any other field of endeavor.

So conditional formatting is putting formatting in a cell,

based on the contents of the cell.

So in this video, we'll teach you two aspects of conditional formatting.

Highlight, top bottom rules first

sorry, and highlight cells.

And so to illustrate how these ideas work and

again this is in data analysis this is not only chapter 23,

if you want to learn much more about it the second longest chapter in that book.

Excuse me, I need a drink of water.

We're going to look at James Hardin, you're the beard and I'm taping this right

after the Rockets made that comeback where they're down 18 in LA and won the game,

and game seven's coming up so I don't know what's going to happen.

Go Rockets.

James Harden, 2014-2015 game logs from the great site again,

basketballreference.com, which I hope you will all be checking all the time.

We'll be using it a lot, as we will be using profootballreference.com.

Okay, so we've got every game that James Hardin played to date and his statistics.

So let's suppose you want to highlight the five games he scored the most points.

You just select, with additional format you have to select the data first.

You select the points column.

You go Home, Conditional Formatting, and

by the way, well I have it on the quick access tool bar here.

Let me just show you a little trick here.

Let's see. Let me delete this.

If you want something up here on your Quick Access toolbar you do a lot,

you just right click, Add to Quick Access Toolbar,

and then you can find it very often.

So I can find conditional formatting up there if I want, but

I'll do it right here.

So, let's go to top bottom rules, and

let's say top ten, let's say I wanted to be the top five.

So his five best games where he scored the most points.

Wow, he's got 50 points, so 50 point games there are in red.

Now If I want his worst ten games,

I could say top bottom rules, bottom ten and I could say green.

So I've highlighted his five best games in red,

his 10 worst games in points in green.

By the way if you'd made this original data table where you added new data,

the formatting would update automatically.

Okay, so how about free throws.

James Hardin, a big key to his success is free throws.

Let's highlight every game where he was above average in free throw attempts,

in a certain color.

So I'll do conditional formatting.

I can do above average, why not just keep red,

and I can do conditional formatting, top bottom rules, below average.

Let's try green again.

So now every game will be highlighted, because he was either above average or

below average.

You can see the games where he has the really high scoring games.

He was above average in free throw attempts, which is not surprising.

Now a question you might ask is, how could I highlight the entire row for

every game where James Hardin was at his top five point score?

That'll be coming up later okay, that's a little bit tricky.

Okay, so actually we did top bottom rules there, so that should,

I guess.

Okay, let's call that top bottom rules 2.

Just call it top bottom.

Okay, and this one will be switched to we'll call it Highlight cells.

Save data set.

Okay, suppose I want to highlight every game where James Hardin scored

at least 30 points.

So then I would select the points column, control,

shift, down arrow, conditional formatting, highlight cells.

There's a lot of choices here.

Greater than, less than, between, or equal are very straightforward.

Text that contains, I could have text that contains any text stream like we would

want to highlight every game where they played Washington.

You could highlight the column with their opponents in say, Washington.

Data occurring i'll do in a minute, but let's say he scored at least 30, so

I should do greater than 29 points.

So any game he scored greater than 29, which is 30 or more is highlighted.

So we had seen that he had some streaks where he got four games in a row above 30,

that seemed to be his longest streak of at least four games above 30 points.

Okay, so now let's look at dates.

So I'm want to highlight any game in the last 30 days,

and so I'm taping this I believe on May 15th, 2015.

Start.

And a couple of quick functions that are nice in Excel,

the today function always puts in today's date.

Used to be Annie Turing's favorite function until they fired her

from the Today Show, I guess, and

I can use a function Excel 2013 formula text to show you that.

And if I want today's date and time,

I'm on Eastern time, even though I'm in Vegas today.

I should've bet on the Rockets last night.

Okay.

So there, that gives today's date and gives today's time,

which is 10:38 Eastern time here where I am.

Okay, but I want to highlight any game played in the last 30 days as of May 15th.

So I can highlight this, and go Conditional Formatting, Highlight Cells,

and there's a lot of options on date occurring, like Yesterday.

I'm just going to do the last 30 days though.

So in the last month.

Okay.

Actually the last month there would be, since it's May,

would be anything that's April.

So I don't, I mean there is a way to do the last 30 days, but

this really won't do that.

So the last month would be anything that basically since we're in May,

it's any game in April.

So all the games in April are highlighted.

Okay, so I think that's probably a long enough video, and

I like to break things up into short videos.

So in the next video, we'll teach you about how you can summarize data

with data bars, color scales, and icon sets.