It’s college football bowl season once again. I have a spreadsheet I use to help me identify winners and losers (for entertainment purposes only).
The four yardage columns are yards per game for rushing offense, rushing defense, passing offense, and passing defense, respectively. I’m trying to identify teams that have better defenses (because defense wins championships). While Wisconsin is rushing for 212 yards per game this season, Florida State is only allowing their opponents to rush for 127 yards per game. This, to me, is an advantage for Florida State.
California is +14 in turnovers, taking possession from their opponents more often than they give it away. That’s turnover margin for the season, not per game. Miami, on the other hand, can’t seem to hang on to the ball. It seems that Cal should get at least one turnover in their bowl game.
The Bowl Wins ratio is bowl games won to bowl games played. Western Michigan (not shown) has won zero bowl games in four attempts. Are they due for a win? Probably not. It’s more likely that their coach doesn’t prepare them well for bowl games.
Columns R & S are thresholds. If the disparity between rushing offense and defense is more than 75 yards per game, I think that’s significant and I want to know about it. If a team has won more than 65% of their games or less than 35%, I want to know. But I can change those values if I like.
The hidden columns J:O are used in the conditional formatting of the other columns. They are:
J: =MOD(ROW(),2)=1 ‘Tells me if I’m comparing the team above or below
K: =IF(J2,D2-E1,D2-E3) ‘Rushing offense differential
L: =IF(J2,E2-D1,E2-D3) ‘Rushing defense differential
M: =IF(J2,F2-G1,F2-G3) ‘Passing offense differential
N: =IF(J2,G2-F1,G2-F3) ‘Passing defense differential
O: =IF(MOD(ROW(),2)=1,H2-H1,H2-H3) ‘turnover differential
P: =VLOOKUP(B2,Bowls!$A$1:$F$119,2,FALSE)/VLOOKUP(B2,Bowls!$A$1:$F$119,5,FALSE) ‘bowl winning percentage
These formulas drive the conditional formatting in D:I. The conditional formats all look something like this:
You can download BowlStats2008.xls.zip
So if you want to go .500 like me, now you have the tools.
Posting code? Use <pre> tags for VBA and <code> tags for inline.