1

I do not know how to dynamically change the RANK formula based on the date.

For each day (or each new date) I have to rewrite the formula in order to match the range of the D-Day.

Date    Name    Points  Ranking Reward
27 Jul  Andy    3          3    6
27 Jul  Mike    14         2    8
27 Jul  Lucy    78         1    10
26 Jul  Andy    45         2    8
26 Jul  Mike    65         1    10
26 Jul  Lucy    12         3    6
25 Jul  Andy    123        3    6
25 Jul  Mike    23423      2    8
25 Jul  Lucy    466566     1    10

See the google sheet here : example

The datas are added dynamically each day so I would like it to be automatic. I am open to try some script as well if needed. Thank you :-)

P.S : I also found this question : similar problem ?

But I did not understand how the formula response works and I am not sure I can apply it to my problem here.

player0
  • 124,011
  • 12
  • 67
  • 124
Code Tinkerer
  • 33
  • 5

1 Answers1

2

use in D1:

=ARRAYFORMULA({"Ranking"; IFERROR(1/(1/VLOOKUP(ROW(A2:A), {
 SORT(ROW(A2:A), A2:A, 0, C2:C, 0), 
 COUNTIFS(SORT(A2:A, 1, 0, C2:C, 0), SORT(A2:A, 1, 0, C2:C, 0), 
 ROW(A2:A), "<="&ROW(A2:A))}, 2, 0)))})

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    Wow ! No chance I'd found this by myself Thank you very much ! It is working perfectly well. I just need to understand what everything does now ! Thanks again – Code Tinkerer Jul 27 '22 at 22:47
  • And one last question : I have another case where I can have multiple entries for one person the same day (see Sheet named "Case 2"). Is it possible to do something similar but with the sum of all the entries of the day for each person ? – Code Tinkerer Jul 27 '22 at 23:42
  • I found the solution thanks to another of your response here : https://stackoverflow.com/questions/56325122/sum-entries-per-date-when-multiple-entries-of-date-exist I used Fx=QUERY(A2:C,"select A, B, Sum(C) where A is not null group by A, B label sum(C)''") So thank you again I guess :-) – Code Tinkerer Jul 28 '22 at 00:28
  • @CodeTinkerer try: https://i.stack.imgur.com/e8Etc.png if you want it ungrouped – player0 Jul 28 '22 at 00:55