Quote:
Originally Posted by Mile High Mania
Amazing work... would be cool to see the Broncos and Chargers... even the Seahawks for old time sake as an overall comparison. I'm not sure if you used the data from profootballreference.com or not, but if I could get you the data... just tell me how you did it.
|
I'd really like to do it for all the teams, and actually have it set up pretty good now. It'd be great if you or others could help. Anyone who wants to do this, here's the system. (The Broncos' index is going to be depressing to us Chiefs fans, though.)
Just take the enclosed spreadsheet and do the following:
Go into pro-football-reference, and year by year grab the schedule/record. It's the page that looks like this:
http://www.pro-football-reference.co...s/crd/2009.htm
You can select the whole thing with a careful click and drag, and then copy the whole thing into cells G through AA. This is the only time consuming part because you have to copy and paste each year individually. However, there's a linky thing at the top that will let you go directly to the next year's table.
It's very important to start with the first year of the franchise at the top of the spreadsheet and work your way down, with the most recent year last. For AFL legacy teams, that means you start with 1960. For other teams, you might be starting in 1920 or 1950 or 1976 or whenever the team was founded.
You then have to do the following:
1. Delete the bye weeks.
2. Cell F is nothing but a count, so it means nothing.
3. In Cell E, insert a 0 (zero) for non-playoff games, and a 3 for any playoff games.
4. Cell D is the number of years since a playoff appearance, but I ended up never using that, so you can ignore it.
5. Cell C is the main formula, so leave it alone.
6. Cell B is the Year. Just a bookkeeping thing.
7. Cell A is some random calculations. The ones at the end of each year are the Hope Calculations, which merely compare the stock index at the end of one year to the next year. There are some other random calculations in there that you can ignore. All of this is optional.
8. You'll need to define the data set to update the graph, or otherwise it might not include the whole time period. That function depends on the version of Excel you have. If you don't know how to do that, just upload the file to me and I'll update the graph.
If your team had more or fewer playoff appearances, you may need more or fewer rows. You can just copy the formula into any new rows you need.
If you have an old version of Excel the file may not open. Let me know.