Going Bowling

If you didn’t know, it’s college football bowl season. The three people who read this blog and like college football may like this spreadsheet.

I’ve copied some stats from the NCAA stat page and pasted them into Excel (Copy from the web page and Paste Special – Unicode Text). Then I organized the stats by bowl match up and applied some conditional formatting. I also show the last 4 games’ results to identify hot teams.

The conditional formatting is driven from the three cells at the top. The stats are national rankings for Rushing Offense and Defense, Passing Offense and Defense, and Turnovers. If the difference in the national ranking between opposite sides of the ball is more than 40, they cells are flagged. For instance, if a team has a rushing offense that’s 10th in the nation and their opponent has a rushing defense that’s 51st or worse, then the offense his highlighted yellow and the defense is highlighted red. The turnover stat isn’t national ranking, but plus-minus. A positive number means the team has more take-aways than give-aways.

You can change the numbers at the top (40, 40, and 8 ) to whatever you think is a better measure. I tried a few different numbers, but these seem to give good results. The result looks like this:

range showing conditional formatting on stats

but shows every bowl game. You can download BowlStats.zip to see the whole workbook. For entertainment purposes only, etc. etc.

Posted in Uncategorized

6 thoughts on “Going Bowling

  1. Happy New Year
    Merry Chrismas

    to all readers and contributors to this Blog.

    I hope Dick’s blog will bring me as much Joy, Wisdom and Tricks in 2006 as it did in 2005.

    Gelukkig Nieuwjaar,
    en een zalige Kerst,

  2. I have a question, I’m new to Excell VB. I am working on a project at work and I run in to a problem and I was wondering in any of you guys could help. From a workbook I need to follow a hyperlink and open a csv file and copy a row and bring the data back and past it in to the workbook I started in. I need to do this on a daily bases the flie names change day-to-day (i.e. 20051225.csv , 20051226.csv) How can I do this in VB.

    Right now I download the file and use a macor to copy the info over. Any help you guys could give me will be helpful Thank you!

  3. Dick, did you remove the file? The link doesn’t seem to be working for me. Perhaps Vegas caught wind of it?

    Alex, you can use the Format and Now functions to modify the file name:

    strFileName = Format(Now(),”yyyymmdd”) & “.csv”

  4. Yeah, there’s no way, according to those stats (or much other way) that Wisconsin should have beaten Auburn, but I sure won’t complain.

    I didn’t realize that USC had a PLUS 22 turnover ratio. That’s an amazing stat all by itself.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.