Amend ranking formula for sports results

**alan_stephen75@** · 10-15-2009, 05:13 PM

I am attempting to upgrade a scoring system in excel 2003 which I use to rank wins in a sports results grid and I am seeking help to amend a formula based on a new version.
The current formula in the attached part-completed worksheet Draw9 of 1 to 9, which I wish to amend, is in cells AM42:53 one of which is:

=IF(AL44="","",RANK(AL44,AL44:AL53)&CHOOSE(AND(RANK(AL44,AL44:AL53)<>{11,12,13})*MIN(4,MOD(RANK(AL44,AL44:AL53),10))+1,"th","st","nd","rd","th"))

I have now added an extra column of data in cell AN44:53 under “LSD” and I would like to use this to improve the accuracy of the original ranking. In other words I wish to Rank teams using the “Wins” in column AL42:53 plus the “LSD” data in columns AN44:53 and this will ensure that where teams are tied on the same number of wins then the numbers in the “LSD” column will enable a clear ranking i.e. say 3 teams on 3 wins ranked 6th = will now be ranked 6th,7th & 8th.

I do hope I have explained it fully and any help would be greatly appreciated.

**daddylonglegs** · 10-15-2009, 05:34 PM

What's better, high LSD?

If so you can try this formula in row 44 copied down

=IF(AL44="","",RANK(AL44,AL$44:AL$53)+SUMPRODUCT((AL$44:AL$53=AL44)*(AN$44:AN$53>AN44))&LOOKUP(RANK(AL44,AL$44:AL$53)+SUMPRODUCT((AL$44:AL$53=AL44)*(AN$44:AN$53>AN44)),{1,2,3,4;"st","nd","rd","th"}))

This is valid for ranking up to 20 teams

**alan_stephen75@** · 10-15-2009, 06:11 PM

Thanks for your very prompt reply and yes I should have made it clear that in fact a low LSD is better.

**daddylonglegs** · 10-15-2009, 06:13 PM

In that case it's the same formula but with the >s reversed to <s, i.e.

=IF(AL44="","",RANK(AL44,AL$44:AL$53)+SUMPRODUCT((AL$44:AL$53=AL44)*(AN$44:AN$53<AN44))&LOOKUP(RANK( AL44,AL$44:AL$53)+SUMPRODUCT((AL$44:AL$53=AL44)*(AN$44:AN$53<AN44)),{1,2,3,4;"st","nd","rd","th"}))