I've got a data set of 365 cells about the wave height per day, I want to figure out how to find when the highest average over 15 days is during the year. I am limited to using google sheets and have beginer to intermediate knowledge of google sheets which makes it hard to figure out how to write the function.
I have no clue how to get there, all i could figure out online is using the function to find the highest 10 values in a dataset, but not consecutive values.
With the use of SCAN, SEQUENCE and INDEX, you can navigate through the range, choosing ranges of 15 cells; and returning the average:
=SCAN("",SEQUENCE(COUNTA(A1:A)-14),LAMBDA(a,v,AVERAGE(INDEX(A:A,v):INDEX(A:A,v+14))))
Just by wrapping that into MAX would give you only the value. If you need also the range, can do something like this:
=SCAN("",SEQUENCE(COUNTA(A1:A)-14),LAMBDA(a,v,{"Rows: "&v&" to "&v+14,AVERAGE(INDEX(A:A,v):INDEX(A:A,v+14))}))
That would return:
With the help of SORTN you'll be able to find the highest value (or values, just change the first 1 after the closure of SCAN with the value you want):
=SORTN(SCAN("",SEQUENCE(COUNTA(A1:A)-14),LAMBDA(a,v,{"Rows: "&v&" to "&v+14,AVERAGE(INDEX(A:A,v):INDEX(A:A,v+14))})),1,1,2,0)
To have the actual values, use:
=LAMBDA(ind,FILTER(A:A,ROW(A:A)>=C1,ROW(A:A)<(C1+15)))(INDEX(SORTN(SCAN("",SEQUENCE(COUNTA(A1:A)-14),LAMBDA(a,v,{v,AVERAGE(INDEX(A:A,v):INDEX(A:A,v+14))})),1,1,2,0),,1))
