Can this Effective Ranking Formula be Modified?

It is virtually 5 years since I sought an answer to making a ranking table more user-friendly. Almost by return, vlady gave me her (I'm assuming!!) solution - which I would never have thought of in a million years! Since that time her VLOOKUP solution has remained the bedrock of our annual ranking lists, for which I remain forever thankful.

From the attached _original.xlsx file you'll see that the VLOOKUP formula takes the competitors' placing in the competition and produces its corresponding value. Based over 4 years the values are decreased by 75%,50% and 25% over the subsequent years. Finally, the formula takes account of a 'weighting' of x2 or x3, depending on the type of competition.

However, we now have a new committee who wish to modify certain aspects of these ranking lists and whilst I have altered one part of the formula to reflect their wish not to use the 'weighting' value of x2 or x3, I am at a loss how to amend - if feasible - the remaining aspects.

In the _revised.xlsx file you'll see the area on the Table for Lookup is marked in green where the value will be recorded as 1 for any competitor placed lower than 10. The powers that be wish this value of 1 to be applied across the years, but of course the formula as it currently stands, re-evaluates to 75%, 50% and 25% - per the orange fields.

Is there a way the formula can be modified to take account of this?

Once the list is complete with the data sort of the total points earned in Col. B, is there a way of generating the ranking position in Col. A automatically, taking account of joint points/positions, as marked in mauve?

I do realise that I'm seeking solutions for which there may not be answers, but I live in hope!

This particular forum has taught me a great deal about Excel and other MS Office topics for which I'm so thankful, but formulae leaves me brain dead!

JeteMc: what can I say - it works perfectly!! This afternoon I've been playing with the revised formula, using various scenarios and it is giving me exactly what I need! I cannot thank you enough for taking the time to work this out for me.

In truth, the actual competition spreadsheets (covering several different types of competition) can extend to 12 data columns, covering the current year and the 3 previous years, taking account of different competitions (Challenge Cup, World Cup, European Championships etc) over that four year period. With sometimes more than 100 competitors the point where a competitor is only awarded 1 point (regardless of the year) is set at the 51st place. The VLOOKUP table is on a separate sheet tab.

I have had to tweak your formula to take account of the location of the Table as well as its size and whilst I had a few hiccups when I first used it, it is now working brilliantly!!

Likewise the ranking works brilliantly - I can live without the equal's sign (=) which I use to denote a joint rank position. I'm feeling pretty dumb right now as I get the impression that it's a basic Excel function that I had no knowledge of!

I can't attach the actual working spreadsheet as it contains competitors' personal data, but the example now attached proves your formula works admirably.

Once again thank you for taking the time and trouble to help me.

JeteMc - I'm totally blown away by your generosity in time and knowledge - please accept my inordinate thanks!