Excel help please - ranking scores

[MesoForm]

Edit - question doesn't need answering, turns out the issue was with a value not being rounded.

I've got a spreadsheet of runner's times and need to rank them from first to last, if two runners get the same time they are given the same rank then the next rank is skipped.

I used AI to guide me and it pointed me towards the rank.eq function which almost gives the correct results



But you can see the rank works for runners F and G but not A and B.

AI just goes round in a loop suggesting
=IF(D2<>"", COUNTIFS(D$2$25, "<" & D2) + 1, "")
then
=SUMPRODUCT((D$2$25<D2)/COUNTIF(D$2$25, D$2$25)) + 1

But they give the same result with A and B getting different scores.

Is there something I'm missing?
Last resort is to insert a runner who always wins and then hide their row but that's far from ideal!

Edited by MesoForm on Monday 18th August 11:13

[bigpriest]

I thought the normal RANK function in Excel behaved as described - identical values get the same rank and the next rank is skipped.

[r-kid]

you are using rank.eq, just use rank, are you sure those number are exactly the same as well?

so just use =rank(b2,$B$2:$B$20,1)

[nvubu]

I'd guess rounding and formatting.

If you remove the format of the Difference column you will probably find that A & B are different numbers.

If so, format the difference column to "00:00" as I guess that is a time?

[MesoForm]

Thanks, but I think I've wasted your time - when writing a this other post in Website Feedback ( link) it appears the first two actually are different even though the numbers are all typed in and not copy/pasted