Formula:
=LET(
A,E7:R29, - input range
B,E6:R6, - input range
C,D7:D29, - input range
D,D2, - input range
E,SEQUENCE(ROWS(A)), - counts rows in A and returns an array: 1,2,3,4,etc
F,XMATCH(D,B,0), - finds the correct column in A that matches D2
G,INDEX(A,E,SEQUENCE(,F,MAX(1,F-4))), - chooses the matching column and (up to... depending on availability) 4 columns to the LEFT
H,COLUMNS(G), - number of columns in G (could be anything up to 5)
I,CHOOSECOLS(G,SEQUENCE(H-1)), - chooses all but the last column - could be 4 or fewer - (these are the ones you want to average)
J,HSTACK(IFERROR(BYROW(I,LAMBDA(x,AVERAGE(x))),""),INDEX(G,,H),C), - calculates the average, row-by-row from I and joins it in a horizontal array with The last column of G (the data from the week in D2) and C (the titles)
TAKE(SORT(FILTER(J,INDEX(J,,2)>0),2,-1),5)) - filters the non zero values from column 2 of the stacked array (J), sorts tehm by th second column in descending order and TAKEs only the first 5
Bookmarks