During the post-game interview with Kirk Ferentz after the Holiday Bowl, where the Hawks beat the USC Trojans, Kirk stated that the past 5 year span was the most Hawkeye victories in the history of the program.
I am kind of an Excel geek and this statement got me thinking, "could I create an Excel workbook that I could enter the number of years to evaluate to determine which block of years had the highest number of wins, losses, which years had the best winning percentage, and which years had the worst winning percentage?" I also wanted to graph the individual years based on the number of years, but then also starting with a specific year.
Example, I want to find out what the total number of wins and losses were for the 10 year period from 1949 to 1958. Also, I want to see the individual years, for the time period, graphed to give a visual representation of the yearly record. At the same time I want to compare, out of the entire Hawkeye Football history, which 10 year period had the highest total wins, losses, best winning percentage and worst winning percentage to the 1949 to 1958 period.
Instructions: As you look at the sheet, should you want to, cell A3 is where you can enter the number of years you want to look at. As you enter the number of years in cell A3, the table on the right will automatically change the year in the "End" column (column L) and the wins, losses, ties, and win % will adjust accordingly for the year block you are comparing. There is conditional formatting for the table that will highlight which block of years had the most wins, most losses, highest winning % and lowest winning % for easy, quick reference.
In cell C2, which is a data validation list of all the years the Hawks have played football, select a year you want to start with and then based on the year range you have entered in cell A3, the end year will dynamically change to the corresponding end year. Also as you change these years, the graph below will change to reflect the individual years' records for those years selected in cells C2 and C3.
If you like this, please feel free to use as you wish.