Forum Discussion

Derharm1's avatar
Derharm1
Copper Contributor
Dec 13, 2023
Solved

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...
excel
Formulas and Functions
  • Patrick2788's avatar
    Patrick2788
    Dec 15, 2023

    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.

Patrick2788's avatar
Patrick2788
Silver Contributor
Dec 13, 2023
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/
Derharm1's avatar
Derharm1
Copper Contributor
Dec 14, 2023

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
    Dec 14, 2023
    You can share with a link for OneDrive, Dropbox, etc.
    • Derharm1's avatar
      Derharm1
      Copper Contributor
      Dec 15, 2023
      https://docs.google.com/spreadsheets/d/18ISUgYcSsvZSzYI2IvoKulJhNm-w_r3j/edit?usp=sharing&ouid=107459650418333218348&rtpof=true&sd=true
      • Patrick2788's avatar
        Patrick2788
        Silver Contributor
        Dec 15, 2023

        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.

Resources