Here's another way that uses a VBA User Defined Function (UDF) to find the group of N (a user input) entries and return a dynamic array to the worksheet for the user input number of entries to test.
You could do a classical ctrl-shift-enter array entry also, but I think this is easier since you do not need to re-select the output range each time you change the number of values to check
The green F2 is the start of the dynamic array and the orange F1 is the Sum of the dynamic array's spill range
Capture.JPG
Option Explicit
Dim aryNumbers As Variant
Function LargestConseq(ValuesIn As Range, Optional HowMany As Long = 30) As Variant
Const MinHowMany As Long = 10
Dim rNumbers As Range
Dim idxEntry As Long, idxHigh As Long
Dim totHigh As Double, totCurrent As Double
Dim aryOut() As Double
On Error GoTo ErrHandler
'error conditions
If HowMany < MinHowMany Then Err.Raise 1000, "LargestConseq", "HowMany too small (" & HowMany & "). Must be at least " & MinHowMany
If ValuesIn.Columns.Count <> 1 Then Err.Raise 1002, "LargestConseq", "Input Range must be one column"
'determine 'real' data range, if entire col entered or first entry is non-numeric
If ValuesIn.Rows.Count = ValuesIn.Parent.Rows.Count Then
Set rNumbers = Range(ValuesIn.Cells(1, 1), ValuesIn.Cells(ValuesIn.Parent.Rows.Count, 1).End(xlUp))
End If
If Not IsNumeric(rNumbers.Cells(1, 1).Value) Then
Set rNumbers = rNumbers.Cells(2, 1).Resize(rNumbers.Rows.Count - 1, 1)
End If
If rNumbers.Rows.Count < HowMany Then Err.Raise 1001, "LargestConseq", "Not enough cells in Input Range (" & rNumbers.Rows.Count & ") for requested number (" & HowMany & ")"
'bring data into array
aryNumbers = Application.WorksheetFunction.Transpose(rNumbers)
'prepare output array
ReDim aryOut(1 To HowMany)
'loop data, sum HowMany enteries, keep track of largest total and it's index
For idxEntry = HowMany To UBound(aryNumbers)
totCurrent = pvtSum(idxEntry, HowMany)
If totCurrent > totHigh Then
totHigh = totCurrent
idxHigh = idxEntry
End If
Next idxEntry
'move largest block of HowMany to output array
For idxEntry = LBound(aryOut) To UBound(aryOut)
aryOut(idxEntry) = aryNumbers(idxHigh - HowMany + idxEntry)
Next idxEntry
'return output array and exit
LargestConseq = Application.WorksheetFunction.Transpose(aryOut)
Exit Function
ErrHandler:
With Err
LargestConseq = .Description
End With
End Function
Private Function pvtSum(idxStart As Long, cntHowMany As Long) As Double
Dim X As Double
Dim i As Long
For i = cntHowMany - 1 To 0 Step -1
X = X + aryNumbers(idxStart - i)
Next i
pvtSum = X
End Function