Forum Discussion
Ranking in Excel
I hope you are all well and avoiding the nasties as we are here in Scotland.
I am at an impasse with one thing however. I have a file that I use for horse racing stats. I have a Formula to ranks the ratings which I create.
The only problem, I think, is that it will rank them but it does not do it without skipping numbers but does ignore the Zeros as required
Ideally I would want it to check Course and Time against the rating but I only have it checking the time at the moment
=IF(AB2=0,"",SUMPRODUCT(--($C$2:$C$500=C2), --($AB$2:$AB$500>AB2))+1)
This is the Ratings column $AB$2:$AB$500>AB2 This is the time column ($C$2:$C$500=C2)
Column B is where the Course resides ($B$2:$B$500=B2)
Should I be using something else as I would Ideally just wish the Top 5 rated to be shown
regards Richard.
RichardBeavers The XLOOKUP errors I found are in columns V and W.
I think I follow you now - suggested formula in the attached.
5 Replies
RichardBeavers Could you explain what you need to do and where a little more? I can't quite follow.
One thing I immediately noticed - your XLOOKUPs refer to a range that has no $s in it - that means that as you copy the XLOOKUP downwards, the lookup and return arrays are also moved downwards, which will cause errors. Try e.g. N$4:N$500.
Hi Savia
Many thanks for your reply.
Ultimately I am looking to achieve a ranking for (Top 5 only) the data in Column AB
This Ranking should relate to the Date Course and Time of the race. Ignoring Zeros and without skipping numbers (where Duplicates) but only showing Top 5 where any rating above that is then a Blank. Not much to ask Eh! (LOL)
eg,
Date Meeting Time 01/03/2020 Huntingdon 14:20 103 2 01/03/2020 Huntingdon 14:20 103 2 01/03/2020 Huntingdon 14:20 88 3 01/03/2020 Huntingdon 14:20 0 01/03/2020 Huntingdon 14:20 133 1 01/03/2020 Huntingdon 14:20 0 01/03/2020 Huntingdon 14:20 0 01/03/2020 Huntingdon 14:20 0 p.s not Sure where the Xlookup mistake is that you refer to is it in a particular column. Probably more mistakes than that given my brain is 70 years old (Almost)
RichardBeavers The XLOOKUP errors I found are in columns V and W.
I think I follow you now - suggested formula in the attached.
