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