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...
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,ξ)
)
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.
