How do I rank a table, formula or macro please?

[RiverRats]

Hi everyone,
Hoping someone can help. I have a spreadsheet with almost 10,000 rows and I have trimmed it down to just 4 columns of interest for this question. (picture attached)
Columns are:
Date
Time
Number
Percentage

Column I want to add is Position:

The sheet is sorted by Date then Time and then Percentage.
The data is broken into blocks which can be sorted by date and time. These blocks will have between 8 and 24 rows. I want to add a column which gives the position of each row in the block based on highest percentage is 1 and so on down the list of the block and start again on the next block. I have done the top 2 blocks manually in the picture.
Block 1, Row 4 and 5 both have 66.7 so they are both ranked as 3 and row 6 then gets ranked as 5.
Block 2, row 19 and 20 are both top ranked at 66.7 so they both get 1 and row 21 gets 3.
Hoping someone can help with a macro or formula please.
Thank you in advance for having a look.

 

[steve the fish]

This do it?

=COUNTIFS(A:A,A2,B:B,B2,D:D,">"&D2)+1

 

[RiverRats]

This do it?

=COUNTIFS(A:A,A2,B:B,B2,D:D,">"&D2)+1

Steve you legend. A super big thank you.

 

[Peter_SSs]

It may not be an issue for you but there is a lot more calculation being done in that formula than is required.
Given the information about how your data is sorted, although the following formula is marginally longer than the one above, it is much more efficient in that it only checks values above the current row, not the entire column of data.

For example with just 39 rows of data like you have shown, the above formula on my machine takes approximately 0.210 seconds to calculate the 38 results whereas the formula below takes approximately 0.005 seconds. That is about 40 times faster.
I understand that, depending on how much data you have, the difference may not be noticeable but for larger data it may.
For 400 rows the first formula takes about 2.0 seconds which is very noticeable whereas the alternative was 0.015, about 130 times faster)

Anyway, if it is of interest, you could give this a try

=COUNTIFS(A$1:A1,A2,B$1:B1,B2,D$1:D1,">"&D2)+1

 

[RiverRats]

It may not be an issue for you but there is a lot more calculation being done in that formula than is required.
Given the information about how your data is sorted, although the following formula is marginally longer than the one above, it is much more efficient in that it only checks values above the current row, not the entire column of data.

For example with just 39 rows of data like you have shown, the above formula on my machine takes approximately 0.210 seconds to calculate the 38 results whereas the formula below takes approximately 0.005 seconds. That is about 40 times faster.
I understand that, depending on how much data you have, the difference may not be noticeable but for larger data it may.
For 400 rows the first formula takes about 2.0 seconds which is very noticeable whereas the alternative was 0.015, about 130 times faster)

Anyway, if it is of interest, you could give this a try

=COUNTIFS(A$1:A1,A2,B$1:B1,B2,D$1:D1,">"&D2)+1

Hell Peter, thank you so much for your input, and sorry for the late reply, I was so happy with the above solution I got carried away for a while. I must say it is quicker (and a better font (ha ha)). With the example I showed above, I did data sort it to make it easy for me to rank them so I could show an example of what i wanted. If I don't data sort before using your formula it goes a little haywire, I guess it is the absolute reference. Sorry for not mentioning that earlier.

 

[Peter_SSs]

If I don't data sort ..

Sure, my formula was based on the data being sorted as per you sample. If it is not sorted then my previous formula is not relevant.

However, I would still recommend not using whole column references. With the original suggestion and nearly 400 rows I mentioned the recalculation time on my machine was about 2.0 seconds. With the formula below (for which the data does not need data to be sorted) the recalculation time is still about 0.015 seconds. Just make sure where I have used 400 that number is equal to or greater than the last row of data is in or is likely to be in for the future.

=COUNTIFS(A$1:A$400,A2,B$1:B$400,B2,D$1:D$400,">"&D2)+1