Mar 21 2023 01:59 PM
The challenge is simple. Find the sub-array (7 consecutive elements) within a 1,000 array vector with the largest SUM total. The vector was randomly generated and there are no duplicates.
My solution is included in the attached workbook. I won't explain yet so as to not influence others' solutions.
Mar 21 2023 02:26 PM - edited Mar 21 2023 02:28 PM
Short but volatile:
=LET(
ζ,SUBTOTAL(9,OFFSET(vector,SEQUENCE(ROWS(vector)-6,,0),,7)),
ξ,MAX(ζ),
HSTACK(XMATCH(ξ,ζ)+@ROW(vector)-1,ξ)
)
Longer but practically non-volatile (volatile only once, at workbook open):
=LET(
ζ,SCAN(0,
SEQUENCE(ROWS(vector)-6),
LAMBDA(ζ,ξ,SUM(INDEX(vector,ξ):INDEX(vector,ξ+6)))
),
ξ,MAX(ζ),
HSTACK(XMATCH(ξ,ζ)+@ROW(vector)-1,ξ)
)
Mar 21 2023 04:07 PM
Somewhat longer but with simple steps
= LET(
init, SUM(TAKE(vector,7)),
diff, DROP(vector,7) - DROP(vector,-7),
Addλ, LAMBDA(x,y, x+y),
sum₇, SCAN(init, diff, Addλ),
max₇, MAX(sum₇),
pos₇, 1 + XMATCH(max₇, sum₇),
HSTACK(pos₇, max₇)
)
Mar 22 2023 05:50 AM
I'm not usually a fan of OFFSET when using 365 where there are better options available, but I do like your first solution. The economy of the formula is impressive. The Greek letters add a nice signature that's used elsewhere.
When I drew up this challenge I though I might expect SCAN to be involved in a solution. The notation is very clear. I enjoyed opening AFE and stepping through the solution to see how this one came together.
Mar 22 2023 06:04 AM
I'd classify my solution as 'workmanlike'. Not as elegant as I'd like, but a nice diversion from some other projects.
Notes:
Lambda - 'Accumulate'. Pull 7 elements from the vector by determining the starting element with XMATCH. HSTACK the row number with the total for those 7 elements. VSTACK to produce a matrix of row numbers with totals.
=LAMBDA(a,v,LET(
totals, CHOOSEROWS(vector, SEQUENCE(7, , XMATCH(v, vector))),
VSTACK(a, HSTACK(ROW(v), SUM(totals)))
))
Sheet level formula.
Sort the 'totals' produced from REDUCE in descending order by the 2nd column. TAKE the top row.
=LET(totals, DROP(REDUCE("", DROP(vector, -6), Accumulate), 1), TAKE(SORT(totals, 2, -1), 1))
Mar 23 2023 06:45 AM
A variation of this challenge: determining longest Win/Loss streaks in a season
'Outcome' - an accumulation of Ws and Ls
=LAMBDA(a,v,IF(v = RIGHT(a), a & v, v))
'Streak function
=Lambda(arr,W_orL,LET(results, SCAN("", arr, Outcome), MAX(LEN(IF(RIGHT(results) = W_or_L, results)))))
Mar 23 2023 02:58 PM
Once one can get away with accumulating a scalar value, SCAN does a really good job!
= LET(
streak, DROP(WinLoss,1) = DROP(WinLoss,-1),
count, SCAN(0, streak, LAMBDA(x,y,x*y+1)),
MAX(count)
)
The trouble is that most problems of interest do not fall into that category.
Mar 23 2023 04:42 PM
This is intended as an alternative solution to your first problem using convolution. The implementation took me days but that was a while back. To use the function is not so bad!
Mar 24 2023 06:14 AM - edited Mar 24 2023 06:15 AM
Before I opened the file, my guess is you did it with 'rolling average' trendline but I see you took it a step further and produced something that really works well visually (I did try adding a 'moving average' trend line but it didn't look as good as your version here). This Scatter chart is really sharp!
It's interesting Excel has some tools tucked away for analyzing data like this. Data Analysis's Moving Average (w/7 set as interval) does produce a rolling average for the data set but does so with scalars and not silky arrays,