Is it possible to average a value over the last 5 days based on criteria?

user_12090346 0
Viewed 39

I am trying to find the average of certain NBA data points over the last 5 and last 10 days. I want to know, for example, how many FG an opponent allowed over the last 5 days. I also want to calculate how many FG an opponent allowed over that time frame to a specific position. How could I go about doing that? I have attached the raw data in a googlesheet below.

https://docs.google.com/spreadsheets/d/1HYliF8kjfTsf9ursxo0yi5SDF02ZruZXeK-WRdxDhmg/edit?usp=sharing

I want to be able to average the data in column M, based on the opponent in column H as well as the date in column A. Eventually I would like to also average that data based on the position, which is in column AB. I appreciate the guidance!

textjoin google-query-language google-sheets-formula flatten google-sheets
1 Answers

average of last 10 days of FG values per each OPP:

=INDEX(QUERY({H2:H, M2:M, B2:B&H2:H}, "select Col1,avg(Col2) where Col3 matches '"&
 TEXTJOIN("|", 1, FILTER(SORT(UNIQUE(B2:B&H2:H), 1, ), COUNTIFS(
 SORT(UNIQUE(B2:B&H2:H), 1, ), SORT(UNIQUE(B2:B&H2:H), 1, ), 
 SEQUENCE(COUNTUNIQUE(B2:B&H2:H)+1), "<="&SEQUENCE(COUNTUNIQUE(B2:B&H2:H)+1))<11))&"'
 group by Col1 label avg(Col2)''"))

for 5 days change 11 to 6

enter image description here

user_56326290
Related