Dynamic Update to Range in RANK Based on Date

[Derharm1]

I am building an NBA stats workbook where I have separate sheets for each stat (points, rebounds, etc.). For each individual stat, I am tracking this data in one sheet and I have the date of the game for that player in column A. I add the stats for the most recent games in rows below the previous day's games (games from two days ago may be rows 1-50, days from yesterday, 51-100, etc.). One of the stats that I am tracking is the difference between the player's average for that stat and how much they got that game (for example, a player may average 20 points a game, but that game they scored 30 points so that cell would be 10). Based on this difference in value, I am ranking the players for that day. Because I have all of the data for this stat for the entire season in one sheet I am currently changing the range for the rank manually using the following formula:

=IF(ISBLANK(R3), "",COUNTIFS($A$3:$A$14,A3,$R$3:$R$14, ">"&R3)+1)

A = Date (A3:A14 are the only players I am tracking that day; A15 would start the next day's games)
R = Difference in average to the stat for that game.

I would like to figure out a formula where all of the range values (A and R columns) would automatically update to only include rows where the date is the same.

Any suggestions?

[#2]

I have an idea of what you're looking to do but I'm not certain about your data's arrangement.

Are you able to share a sample workbook or even point us to a page on basketball reference with something comparable?
https://www.basketball-reference.com/

[#3]

@Patrick2788 Thank you for the response!

I am trying to upload a sample of the table, but it says that .xlsx files are not supported. I pull the data from basketball reference to get the update stats each day and then I track them myself. I am using the lines provided by draftkings and fanduel as the comparison between their average stat and the difference in the line. 

Do you have another way that I can upload a sample sheet?

I've attached a screenshot of the sheet too if that is OK.

EDIT - I realize that the screenshot did not upload for some reason. Any suggestions for uploading the sheet?

Thank you!

[#4]

You can share with a link for OneDrive, Dropbox, etc.

[#5]

https://docs.google.com/spreadsheets/d/18ISUgYcSsvZSzYI2IvoKulJhNm-w_r3j/edit?usp=sharing&ouid=10745...

[#6 · Accepted solution]

@Derharm1 

Thanks for uploading the sample.

This will work if you're in Excel 365. I created a few dynamic ranges so this formula will be dynamic:

=LET(
    GRank, LAMBDA(a, q,
        LET(
            game_date, SORT(FILTER(L5_LI, Dates = a), , -1),
            IF(q = "", "", XMATCH(q, game_date))
        )
    ),
    MAP(Dates, L5_LI, GRank)
)

Added some dummy data for 11/13 to test it.

[#7]

Thank you @Patrick2788! I have excel on Mac so when I try to select the formula it is grayed out and I cannot copy/paste it. I assume that's the reason I am having that problem?

[#8]

@Derharm1 

The formula result produces a spill. If you're looking to copy the formula, it can be selected by going to the top leftmost cell in the spill and copying the formula.