Forum Discussion
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...
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.
https://docs.google.com/spreadsheets/d/18ISUgYcSsvZSzYI2IvoKulJhNm-w_r3j/edit?usp=sharing&ouid=107459650418333218348&rtpof=true&sd=true
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.
- 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?
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.