Okay, very important concept in sports analytics or money ball is strength of schedule, particularly in the NFL. Now, in the NBA, the western conference teams play a tougher schedule, and the eastern conference teams play an easier schedule. But in the NFL, the schedule depends on where you're finishing your division. And so we'll see that there's a huge difference in the strength of the schedule for the team with the toughest schedule, and the strength of the schedule for the team with the easiest regular season schedule. So lets go back to that NFL 2013 data. And we just want to look at regular season games when evaluating strength of schedule. Okay, so the strength of schedule. Is the average rating of the teams that you've played. So like when I look at the Arizona Cardinals, I look at every game, what was the rating of the team they playing? And I average those, that would be the strength of schedule. So now how can we figure this out? Well what we would need to do is figure out for each game, how good was the home team, how good was the away team. Then like when Arizona was home, add up the away teams strengths. When Arizona was away, add up the home team's strengths and then divide by the number of games. We can use sumif, countif, averageif, whatever. I think we'll use some if if counted. All right, okay, so, the home team strength. Again, I'll do Ctrl+1, alignment wrap text. And I'll do the away team's strength. Okay, and I want to do ranged mains here. Yeah, let's get this in here. So how good was the home team? I'd just look up their rating, not their, the score of the game. So, that's the home team. I come over here, I think I named that. So I say come up walls. Let's check that. We got that. So, when Denver with a home team, they were 11 points above average. Good. And now if I copy that across I should get the away teams rating I believe, because look up is like dollar sign. So Baltimore Ravens, how good were you? Minus 3.52. So I could double click and copy. I should do an if error on that because we've got all these nasty robes. And we'll just put a blank in there, because Excel will ignore blanks in calculations. All right. So now let's just count how many away team games, how many home games. So I think I'd name this column, well we haven't named these columns so let's name them. So select these five columns. We'll do formulas, create from selection, names in top row. Not the left column. Okay, select the name, away team is the away team column, home. I go to the F3 key, you can see home team strength, away team strength, so I think I've got all the names I need. So let's count how many home games. So we'd say count- let's do it, we can do it from the functions. Okay, so I go to the function wizard, go under all functions, and I'll do count it. Okay. So how many home games? So I look under the home team column and if that would match, This name that would mean Arizona was the home team. Hopefully I get eight there, that should get eight away games I hope. Okay, I screwed up there because I need to dollar sign the O. Dollar sign the end. Then I can copy this across. Okay, so then this should be an e, and it is, double-click and copy it down. So every team has eight home and eight away games. It's comforting. See if that's true. So when we are home what's the opponent's trick. So that would be a sum if, if the home column equals this team average the away team's strength. So I think we could use sum if. So if the home team Would be Arizona. Then I would add up the opponents score, F3 the away teams. That says, okay, if the home team is Arizona, add up the away team's spread. And Arizona played a pretty tough schedule, because that division that year was St Louis, Arizona, Seattle, and San Francisco. And poor St. Louis had to play all those good teams. So that's going to be tough on them. Now, when we're away what's the opponent's strength? So, let's use the function wizard there. So we would do SUMIF. Okay, if the away team, see the ranges make this so simple, was Arizona. I am going with that Mark Lindsay's song, Arizona. You probably do. Okay, now the Sum_range. Okay, so when Arizona was the away team, we should average the home teams, we should add up the home teams. So let's check that, okay. If the home team equals Arizona, add up the away teams. If the away team at equals Arizona, add up the home team's strength. Now if I add these all together and divide it by the number of games we should be good. Let's put in a blank column. Probably one more. So the schedule screen, we would just add up. Gain alignment wrap text. There we go. And then we should rank the schedule just rank the one mediums the toughest schedule. So if I add up the strength of the teams in the home games. When the team was home, add up the opponents strength of the away and I know there are 16 games so I can divide it by 16. So Arizona played a schedule that was three points tougher than average. Probably just want to make this two decimal points. Okay, so like the poor St. Louis Rams play in schedule that was three points tougher than average. Not surprising. So now let's rank those schedules to strengths. We say rank this number in this whole column. I think comma zero makes the biggest number get a one. So if I double click and copy that down. So the toughest schedule, actually it's Tampa Bay, I was wrong. St. Louis was second. So Tampa Bay, because Atlanta was pretty good that year. 3.64 points above average. Maybe I'm wrong about Atlanta though, no they weren't per refuse. Tampa Bay 3.64 was the toughest schedule. Now the schedule who is number 32. The Lions minus 2.8. Now what's the difference there? Okay, so that difference is six, the difference there is 3.64 minus a minus 2.8. And we'll multiply that by 16. Points per season. That's 103 points. Now remember 30 some odd points is one win. So this is basically a three win difference in schedules. And now you know, in the NFL [LAUGH] three games is a lot. I mean, it's the difference between 10 and six and [LAUGH], seven and nine, which is the difference between a coach getting fired and a coach getting an extension. Okay. So I mean, you really gotta look carefully at schedules frame when you're evaluating how well your team did like the Houston Texans. I spent a lot of time in Houston last year, and the Texans were nine and seven but they played the easiest schedule. So no offense, I don't expect in 2015 fantastic things from the Texans because they probably won't get the easiest schedule again. Okay. You can bet you'll have a test question to where you'll have to put your schedules. But I think we did a pretty good job at explaining that, and of course this could be applied to any sport, but in the NFL, it's by far most important. Because basically there's just a real difference in schedule. I mean, it's not true that every NFL team plays every other NFL team. because you'd have to play 31 games to make that happen, at least. So I mean, and we only play 16 games. Okay, we'll see you in the next video, when we try and predict the total score of a game. And if you bet on the over under this would be very important to you, not just how much, not just predicting by how much each team wins.