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.

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.

      • Derharm1's avatar
        Derharm1
        Copper Contributor
        Dec 18, 2023
        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?

Resources