Home / Columns / Spread Sheets

Spread Sheets

regarding a certain bunch of birds

Photo by Todd J. Van Emst via ESPN.com

$800,000 later:  what Jerry said.

I don’t take a whole lot away from that game that we weren’t already aware of:  the offense will flat stone a team if you give it the chance – and to date, they always have.  Also, our second- and third-string defenders need a LOT of work.  I don’t doubt there’s talent there, but wow, thirty points is about 17 more than I would have enjoyed seeing.  Auburn players, I implore you as a physician and pediatrician:  avoid the flu like the plague.  Wash your hands, cough into your elbows and pray we don’t get what Ole Miss is getting.

Beyond that, I’m mostly looking forward to the Tennessee game.  War Eagle!

God Girl Grill Gridiron… graphs?

One of the greatest joys of football – and in particular, college football – is its eternal complexity.  Twenty-two men with different yet overlapping and constantly fluctuating tasks square up on a gigantic field and play a game that is at once heavily regulated and stunningly diverse in tactic and strategy.  Each year brings the rise or supercession of some stratagem, hypermodern concepts are born in blazes of shotgun glory and stolid, leathernecked methods recall echoes of a gritty past.  And the past few years have been particularly fertile.  The spread is no longer a sleeper concept, but something every coach has to know about and potentially be able to implement.  The speed option and the zone read seem about as common as the inside zone and the fullback lead.  And speaking of the fullback – ubi sunt?  The world is brave and new, equal parts lunch pail and laptop.

And for a guy (nerd) like me, all that complexity is even more fertile ground for analysis.  In my undergrad as an engineering student, I got to know MicroSoft Excel and haven’t been able to look back.  It was only natural that I’d try to make a spreadsheet out of a football game.  So periodically, you can expect a Spread Sheet – 50% public experiment, 50% thoughtful analysis.

I owe a tremendous amount to Chris Brown’s amazing football blog, Smartfootball.com, in understanding both the mechanics of how the game is played and the methods used to analyze it.  Again, as Jerry says, read Smart Football or lose.

a game of distances

The question is, what’s the best way to look at a team’s performance on the whole?  We basically have three important units by which we measure a football squad.  The first – and most ultimately important – is the scoreboard.  The second is yards, either yards given up by the defense or yards claimed by the offense.  And the third is turnovers.  Being an engineer and seeing three units, my impulse is to combine the three into some useful metric.  According to this article at Sports Quant, fumbles and interceptions both result in about the same amount of lost points or yards.  Smart Football reader Brad Eccles makes the case that fifty yards is a statistically-reasonable penalty for either a fumble or interception – that is, if you fumble, subtract fifty from your offensive yardage total.  I’ve also seen (though I can not remember where, so let me know if you run across that number or a better one) that fifty yards is an appropriate bonus for plays that result in a touchdown.  This seems about right to me.  Thus, we can convert turnovers and touchdowns into yards and express a team’s production using a single unit.  Moreover, what’s good for me is bad for my opponent and vice-versa: penalties against Auburn should be recorded as negative yards, and penalties against opponents as positive yards.

This includes the offense and the defense: the offense’s forward progress can be expressed as positive yardage, and the defense’s as negative yardage.  As we learned last year, the offense and defense are not distinct.

I’ve borrowed another idea from Smart Football.  There, he describes a method of evaluating a play’s success called the Sharpe Ratio.  Now, I have my own issues with the Sharpe ratio – dividing by standard deviation is not a good idea when, in particular, most running backs’ success depends on their big-play potential.  However, one important thing contained in that statistic is the evaluation of a play in comparison to its risk.  We could theoretically run a short screen, a slant, or a quarterback draw every single play and gain a dependable, undefendable two yards every single time.  While this would not be an effective strategy, all plays must be evaluated against this standard.  For instance, an incomplete pass is essentially two yards given up forever.  Thus, the yardage that results from each play is adjusted down by two.

I usually make some judgment about what constitutes “garbage time” and ignore that yardage.  If one team is clearly bleeding clock, they’ve essentially stopped running their offense and it shouldn’t count.  Also, I ignore what I consider to be uncoachable plays or dumb luck, like interceptions returned for touchdowns – these don’t count as an interception and a touchdown, just as one or the other.  If the defense gets the ball, they’ve won and a win is worth fifty.  Same thing for special teams touchdowns.

By this method, we can chart out our team’s progress over the entire game, in both phases of the game, as a single function.  This is just what I’ve done.  I copy the play-by-play at ESPN.com (more reliable than Rivals) into a text file, import that text file into a spreadsheet, and use the wonders of MS Excel to semi-automatically create a running list of the plays that are run, the players who run them, the down, the distance, the location and the result.  Then, once I have an adjusted yardage for each play, I graph a running sum of offensive and defensive yards – this is essentially a running, risk-adjusted yardage differential.  It gives some idea of the arc and flow of the game, and unites the offense’s and defense’s statistics.  The goal, obviously, is to be in the positive by the last whistle.  Granted, this does not reliably correlate with a team winning the game – field goals, defensive touchdowns, and special teams touchdowns are not included.  But that’s not the point – I just want to see which team is most effective when they get down to work.

In addition to game-by-game stats, Excel will dish me out the player-by-player and quarter-by-quarter statistics. I hope I can tease something interesting out of this mess of data.  For instance, Kodi’s and Todd’s average adjusted yards per play in 2008 were 2.7 and 1.5, but in 2009 they are clicking right along at an impressive 9.7 and 12.6.  Likewise, our rushing and passing differentials have improved from -0.2 and -1.03 in 2008 to 1.37 and 5.0 in 2009.  In the meantime, take a look at the graphs of our yardage differentials from this season and the last.  Just so you know, UGA is silver because Arky took red, cupcakes are aqua or pink, and the Iron Bowl is grey for, well, iron.

let’s get this over with


2008 runners

2008 looks exactly like the unmerciful hell that it was.

What’s that?  Couldn’t we take all the games and string ‘em together in one long running sum of our yardage, you say?  One step ahead of you:

2008 master

That tiny white cross, right at play #845?  That is the first play run by the esteemed Messrs. Nall and Ensminger, en route to our spectacular face-plant in the back half of our season.  Goes to show that even if your OC is a jerk and even if you never ran his offense in the first place, the two-headed monster with a track record of FAIL that takes his place will not outperform him.  Ignore that last little downtick if you want to hang on to your chicken salad.

not to ratchet that which should not yet be ratcheted (you know what I mean)

2009:  this graph is significantly more pleasing.  Significantly:

2009 runners

Positive at the end of every game, either in a convincing or incredibly resilient fashion.  Love it.  One thing I find extremely interesting:  there is an inflection point in each of those lines.  Before that point, we play a kind of give-and-take game where neither team has a clear advantage.  And then, something happens and it’s off to the races.  Versus BSU, it happens very early, and versus WVU it happens late – but it has always happened.  I don’t know if that’s merely good coaching adjustments, or if it’s a conditioning issue.

And the running, season-to-date yardage total?  Here it is.  The thick line is 2009 through four games, and the thin line is 2008 – through five:

2009 master

While our competition hasn’t been nearly as stiff this year, you can see how stunningly different is this Auburn team, both in the number of plays run and the result of those plays.  We don’t have much of a standard of comparison between the two seasons as yet, but we did play MSU and WVU in 2008.  How do the two seasons stack up?  Look and see:

MSU 08-09

Notice that in 2008, we should probably have lost this game.

WVU 08-09

boom goes Pythagoras

Success for a football team has to start with moving the chains.  If you can’t get first downs, you can’t (or must be exceedingly lucky to) win the ball game.  To get a metric of success, we can sum the offense’s average total yards over downs one, two and three.  A bare minimum of success is averaging ten yards over the first three downs, or, because we have adjusted our yardages for risk, averaging four adjusted yards.

Now, you may or may not have heard of something called “Pythagorean Win Percentage.”  It’s a way of looking at how many points a team has scored and given up, and extrapolating to how many wins that team should have.  This is what the calculation looks like:

It bears some resemblance to the Pythagorean theorem, and as such, it got named after it.  To use this theorem in the realm of football, the exponent “x” should be 2.37, according to this helpful article at the Pro-football-reference.com blog.  How does this apply?  We can convert from yards to points just as we convert from points to yards.  This is not meant to imply that all yards or drives are created equal, just that a Pythagorean estimate should be able to be calculated from the yardage totals and that we should use a consistent metric.  We can go from yards to “simulated points” by dividing yards by fifty and multiplying by seven.  Plugging that into our formula, we get this:

Now, if we use that average yardage over three downs – the success metric of the offense – as the yardage in this Pythagorean calculation, we can get an estimate of which team “won the game” based on their performance on those critical three downs.

And finally, in a bit I’m calling “Pythagoras vs. Malzahn,” we can graph out Pythagorean third-down wins versus the percentage of snaps that Auburn takes.  After all, Malzahn’s offense is predicated on running at least eighty plays a game – we should be taking more snaps than our opponents if the scheme is working.  Thus we have the graph – the dots and lines are colored to indicate our opponents, and the green cross is an overall performance.  First 2008:

2008 P vs M

And next, 2009:

2009 P vs M

If all goes as planned and Gus really gets this thing hummin’, I think we can expect to see a direct relationship between play percentage and Pythagorean win incidence.  But that remains to be seen.

Email me with your thoughts, questions, corrections, or if you just want a copy of my data and spreadsheets.  I’m more than happy to share my methods and data:  [email protected]

Photo by Van Emst.

About John

John Magruder has been going to Auburn games since before he was born, and when Bo went over the top, he was at Legion Field. Some mothers play Mozart to their developing progeny. John was raised on the roars of the Tiger faithful.

Check Also


The AU Wishbone Podcast: They’re Never Gonna Keep Us Down

Van and John return after the “Tragedy of the Lost Episode” to talk LSU, Louisiana-Monroe, …