Forum Discussion
Derharm1
Dec 13, 2023Copper Contributor
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?
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.
- Patrick2788Silver ContributorI 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/- Derharm1Copper 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!
- Patrick2788Silver ContributorYou can share with a link for OneDrive, Dropbox, etc.