A colleague has devised a method of ranking athletes over a perpetual 4 year period. Using his criteria I devised the spreadsheet, but its annual update is somewhat tedious, consequently I’m wondering whether there’s another method to achieve the same thing.
I have attached a few rows of the Excel 2010 spreadsheet that I am currently using, plus some explanations of the criteria behind the workings.
Each year a major competition is held that attracts many entrants, with the competition in even-numbered years having a greater kudos than the one held in odd-numbered years, hence the Championship Cup has a weighting of 3 x, as opposed to 2 x for the Challenge Cup.
The competitor in 1st place is awarded 100 points, with each subsequent placing given diminishing amounts, ie 99, 98, etc. The weighting is then applied, depending on which competition has just been held.
Finally, the points awarded degrade annually with the current year being 100%, year 2 being 75%, year 3 being 50% and the final year at 25%.
As I mentioned in my opening comment, the annual update is tedious, as in its current form every cell has to manually amended to take account of the factors noted above. Can anyone suggest a way round this, but keeping the general parameters to produce a ranking over 4 years. I might add that my use of Excel is very basic.
Bookmarks