finding the highest total for a consecutive group of entries in a column - EXCEL

Question

rather then finding the highest single entry, i want the highest total for a consecutive group of n entries e.g within a column of 100 values i want to look to find 10 consecutive cells whose sum is a maximum

I say 10 as an example i wish to be able to change that easily.

Answer 1

By far the cleanest way to do this is with a User Defined Function (UDF).

Here is a small routine that will do it. Place this routine in a standard code module:

Function MaxN(n&, r As Range)
    Dim i&, j&, m#, t#, v
    v = r.Value2
    For i = 1 To UBound(v)
        If UBound(v) - i + 1 >= n Then
            t = 0
            For j = i To i + n - 1
                t = t + v(j, 1)
            Next
            If t > m Then m = t
        Else
            Exit For
        End If
    Next
    MaxN = m
End Function

Then on the worksheet where you want to calculate the max, pick a cell where you'd like to have the calculation return and enter this formula:

=MaxN(10,A1:A100)

That's it.

With this UDF you can easily change the '10' to whatever you like and you can easily adjust the vertical range location and size by altering the formula.