# Thread: Conditional Ranking Thanks: 0 Likes: 0

1. ## Re: Question On Conditional Ranking

Try this starting in G2 on down to breakup ties (presumes any given date-time group is less than 1000 consecutive rows).

=COUNTIFS(A:A,A2,B:B,B2,F:F,"<="&F2)-COUNTIFS(A3:A1000,A2,B3:B1000,B2,F3:F1000,F2)

2. ## Re: Question On Conditional Ranking

So here are some from the 2018 calendar year file. It's all the same stuff; groups of data where dates are the same and different times. I added the correct ranking manually and sorted them into rank order to make viewing easier.

There is nothing wrong with missing a ranking number, as if there are 3 that rank as 1, then the next number is 4 and not 2. That's a pretty standard ranking convention.

 Date Time Number Rank 31/12/2018 15:25 0 1 31/12/2018 15:25 6 2 31/12/2018 15:25 8 3 31/12/2018 15:25 13 4 31/12/2018 15:25 14 5 31/12/2018 15:25 20 6 31/12/2018 15:25 21 7 31/12/2018 15:30 3 1 31/12/2018 15:30 5 2 31/12/2018 15:30 7 3 31/12/2018 15:30 9 4 31/12/2018 15:30 9 4 31/12/2018 15:30 11 6 31/12/2018 15:30 11 6 31/12/2018 15:30 13 8 31/12/2018 15:30 14 9 31/12/2018 15:30 16 10 31/12/2018 15:30 16 10 31/12/2018 15:30 19 12 31/12/2018 15:30 19 12 31/12/2018 15:30 23 14 31/12/2018 15:35 4 1 31/12/2018 15:35 4 1 31/12/2018 15:35 4 1 31/12/2018 15:35 5 4 31/12/2018 15:35 8 5 31/12/2018 15:35 9 6 31/12/2018 15:35 9 6 31/12/2018 15:35 10 8 31/12/2018 15:35 13 9 31/12/2018 15:45 2 1 31/12/2018 15:45 5 2 31/12/2018 15:45 6 3 31/12/2018 15:45 6 3 31/12/2018 15:45 7 5 31/12/2018 15:45 7 5 31/12/2018 15:45 9 7 31/12/2018 15:45 14 8 31/12/2018 15:45 15 9

I hope that makes it clearer. The dates span all of 2018 and in the 2019 file, it spans all of 2019 to date. There are anywhere from 4 or 5 per group to 20 or so.

cheers

3. ## Re: Question On Conditional Ranking

Thanks so much for your reply, AlphaFrog. I actually don't want ties broken up, as that is not the standard ranking convention. Think of it like a horse race or any event where competitors race. If there is a dead head for 1st, the next placing is 3rd, not 2nd; a tie for 3rd and the next placing is 5th and so on. It certainly starts at the correct ranking number now, with the first ranking always being 1, but if we can eliminate the breakup of ties, it will be absolutely perfect.

To your question, yes, the most you'd find in any group is around 20 or so; so maybe I can cap it at 30 for safety. I can always adjust that number

Here is one group I ran it on and you can see it split the three entries which all had a zero where they should all rank as 1. I have sorted them so it is easier to view.

 0 1 0 2 0 3 6 4 7 5 9 6 9 7 9 8 10 9 11 10 14 11 14 12 16 13 22 14 22 15

Thanks so much for your help, AlphaFrog

Originally Posted by AlphaFrog
Try this starting in G2 on down to breakup ties (presumes any given date-time group is less than 1000 consecutive rows).

=COUNTIFS(A:A,A2,B:B,B2,F:F,"<="&F2)-COUNTIFS(A3:A1000,A2,B3:B1000,B2,F3:F1000,F2)

4. ## Re: Question On Conditional Ranking

Originally Posted by honkin
i actually don't want ties broken up, as that is not the standard ranking convention. Think of it like a horse race or any event where competitors race. If there is a dead head for 1st, the next placing is 3rd, not 2nd; a tie for 3rd and the next placing is 5th and so on. It certainly starts at the correct ranking number now, with the first ranking always being 1, but if we can eliminate the breakup of ties, it will be absolutely perfect.
=countifs(a:a,a2,b:b,b2,f:f,"<"&f2)+1

5. ## Re: Question On Conditional Ranking

Thanks so much AlphaFrog. That seems to have done the trick.

cheers

Originally Posted by AlphaFrog
=countifs(a:a,a2,b:b,b2,f:f,"<"&f2)+1

6. ## Re: Question On Conditional Ranking

Hi again AlphaFrog

I do have one quick question re this code, if I may. Should it also work on decimal numbers, as I just tried it on a column of numbers with one and two decimal places and everything showed as ranking 1?

cheers

7. ## Re: Question On Conditional Ranking

Yes it should work with decimals.

8. ## Re: Question On Conditional Ranking

My bad AlphaFrog. I realised late that I had autocalculate turned off. It's a nightmare having it on with 100k+ rows, as once you do anything, it wants to recalc and you're waiting for ages. Once I did a recalculation on the sheet, it picked them up. Sorry for the bother.

cheers

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•