Forum Discussion
Challenge: Find the largest Sub-Array (7 consecutive elements) within a 1,000 element vector
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 s...
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))
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)))))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.
