Forum Discussion

Derharm1's avatar
Derharm1
Copper Contributor
Dec 13, 2023

Dynamic Update to Range in RANK Based on Date

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?

  • 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.

    • Derharm1's avatar
      Derharm1
      Copper Contributor

      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!

       

       

      • Patrick2788's avatar
        Patrick2788
        Silver Contributor
        You can share with a link for OneDrive, Dropbox, etc.

Resources