Challenge: Find the largest Sub-Array (7 consecutive elements) within a 1,000 element vector

Silver Contributor

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. 

 

 

8 Replies

@Patrick2788 

 

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

 

@Patrick2788 

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₇)
  )

@JosWoolley 

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.

 

@Peter Bartholomew 

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.

@Patrick2788 

 

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))

 

@Patrick2788 

 

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)))))

 

 

@Patrick2788 

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.

@Patrick2788 

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!

image.png

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,