Super Bowl Analysis

Every so often my worlds collide like when a football game is so popular that non-football fans are aware of it. This week a bunch of nerds will use Excel to analyze the game and I don’t want to be left out. I’ve isolated what I believe to be the most important factor and data-analyzed the hell out of it.

I think we can all appreciate that the 3D effects really drive the point home. And did you notice the use of color? I know. I’m a genius.

My favorite talking head quote about Super XLIX:

For many people in my family, the advertising shown during the Super Bowl provides as much or more entertainment than the game itself.

You mean a bunch of people only care about the ads? What an insightful thought – had you had it in 1975.

MLB Runs By Inning

There is an office pool for the MLB post season that goes like this: Each participant is assigned a half inning per game in the ALCS, NLCS, and World Series. A participant may get the top of the 1st in one game and the bottom of the 6th in another game. There are 20 participants for each half of 10 innings and innings after 10 roll around to the beginning. Every run scored in an inning assigned to you earns you a point.

The challenge is to distribute the innings among the participants. If you always get the bottom of the 10th (an inning that’s rarely played for you non-sportsballers), you wouldn’t fair very well. But what other innings are traditionally good or bad? Is the first inning littered with runs while the fourth is sparse? I couldn’t find any historical data online, but I do have the results of a pool from last season.

Based on this data, I don’t think one post season (21 games) is enough. Why would innings 3, 6, and 7 be higher? Why would the bottom of the innings total 68 runs, 24 more than the top of the innings cumulative 44 runs?

I thought I could assign some sort of value to each half inning, then make sure that each participant got roughly the same value. But I didn’t expect such disparity in innings nor in top/bottoms, so I’m not sure that works. I think I’ll end up making sure everyone gets the 10th inning once, then an even distribution between 1-3, 4-6, and 7-9. And if someone gets the bottom of the 9th more than once, bad luck for him.

Is Patience a Virtue?

If you follow Nebraska football (and let’s be honest, who doesn’t?), then you know that Bo Pelini has been on and off the proverbial hot seat all year. Not surprisingly, Nebraska fans can’t see past the most recent game, so the seat gets hot after a loss and all is well after a win. I have been solidly in the pro-Bo camp. Not because I think he’s a great guy. He’s not. But we picked a horse and we need to stick with it to the end. You can’t go through all the hard times and then kick the guy out. That’s just stupid. We’ve been through six years of Pelini maturing as a coach, so let’s reap some of the benefits.

Then Iowa happened. There’s no shame in losing to Iowa; they’re a fundamentally solid team that makes very few mistakes. Nor is it “the way we lost” as has been said about Wisconsin and Georgia last year – horrifically embarrassing blowouts. What was noteworthy about yesterday was the way Pelini coached. A flea-flicker on the first play from scrimmage? A fake punt from deep in his own territory? It didn’t come off to me as a man trying to get that ninth win and keep his job. No, it seemed more like a man who already knew his fate and didn’t care about winning or losing. Following that was the post-game presser. Pelini said “chicken shit” on live TV, called out the referees (generally a no-no), and blamed the media for hurting the program. He came off like a colossal douche.

After all that, I still think we should keep him. I think he will be fired, I just don’t think he should be fired. (I have a different opinion about Offensive Coordinator Tim Beck, which you know if you’ve been within 100 yards of my house on game day.) For all the things I like about Bo Pelini, all I really want are national championships. Hell, I’d let Steve Spurrier coach here if he delivered that.

So the question becomes: Does firing Pelini get us closer or further away from a national championship? Yesterday DA and I did some back of the envelope analysis that I wanted to formalize today. Our hypothesis was that national championship coaches are hired, not built. Based on the last 20 AP national championships:

The green lines are coaches that won their first national championship within their first five years. Yellow is six to ten years. Red lines require patience. That makes a pretty good case for keeping the coaching carousel churning. That’s a lot of coaches winning national championships with athletes they didn’t necessarily recruit.

Conclusion: Hire a good recruiter, fire him after four years, then hire a good tactician. Or just hire Nick Saban – that works too.

On to some Excel stuff. The formula in E2 is {=MIN(IF($C$2:$C$21=C2,$A$2:$A$21,""))-D2}. It’s an array formula that finds the earliest year that coach won a national championship and subtracts the year he was hired.

I wanted to use the fancy built-in conditional formatting to color the lines, but I couldn’t figure it out. There is a color scales option, but apparently it only applies to the cell and I couldn’t base the whole row’s color on column E. I had to roll my own color scales.

I changed the fill color of the whole range to red. That’s my default formatting. Now I can use conditional formatting to override that as the data warrants.

The “Stop if True” is important here so that future conditions aren’t evaluated.