SOLVED

# Dynamic Update to Range in RANK Based on Date

Copper 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?

7 Replies

# Re: Dynamic Update to Range in RANK Based on Date

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?

# Re: Dynamic Update to Range in RANK Based on Date

@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!

# Re: Dynamic Update to Range in RANK Based on Date

You can share with a link for OneDrive, Dropbox, etc.

# Re: Dynamic Update to Range in RANK Based on Date

best response confirmed by Derharm1 (Copper Contributor)
Solution

# Re: Dynamic Update to Range in RANK Based on Date

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.

# Re: Dynamic Update to Range in RANK Based on Date

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?

# Re: Dynamic Update to Range in RANK Based on Date

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.

1 best response

Accepted Solutions
best response confirmed by Derharm1 (Copper Contributor)
Solution

# Re: Dynamic Update to Range in RANK Based on Date

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.