Ranking in Excel

[RichardBeavers]

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.

[#2]

@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.

[#3]

@Savia 

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)

[#4 · Accepted solution]

@RichardBeavers The XLOOKUP errors I found are in columns V and W.

I think I follow you now - suggested formula in the attached.

[#5]

@Savia 

Should have asked you a week ago.

Many thanks I will correct the Xlookup Errors

Your Formula is great. Is there any way for it to do the same without skipping numbers Savia.

i.e. 1,2,2,3 instead of 1,2,2,4

regards

Richard

[#6]

@Savia 

Hi Savia

I have corrected the Xlookup Errors as you advised.

Still not sure how to get it to 1,2,2,3  Instead of 1,2,2,4

regards

Richard