Find sum of next three column values (observations) after SUM of previous observations reached a certain level

[smide]

Hello.

In column B I have a list of values (observations) and in column C I calculated the sum of those observations.
Somehow I need a sum of next three observations after SUM in COLUMN C (TOTAL) reached certain level (number).

Example.

Finding sum of next three values in column B, after Total (column C) reached at least ten (10) or more.
Result(s) should be placed in column E (E2:E).
Input cell (which contains required "level", 10 in this case) is cell D2.

[TABLE="class: grid, width: 250, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Observations
[/TD]
[TD="align: center"]Sum of Observations
[/TD]
[TD="align: center"]required "level"
[/TD]
[TD="align: center"]Results
[/TD]
[TD="align: center"]...
[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]-8
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"]-2
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]12 [/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"]-4[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"]-7[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD="align: center"]-2[/TD]
[TD="align: center"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD="align: center"]....[/TD]
[TD="align: center"]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD="align: center"]....[/TD]
[TD="align: center"]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD="align: center"]....[/TD]
[TD="align: center"]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Results in column E (adding values from column B): -4+3-7 = -8 , (B6+B7+B8)
-7+2+3= -2 , (B8+B9+B10)

 

[mburrows]

Could you explain what the column D is supposed to represent, the "level"?

How have you decided where your total numbers matter? I assume it is only after the bold ones that you want to use in working out your E column?

 

[smide]

Could you explain what the column D is supposed to represent, the "level"?

How have you decided where your total numbers matter? I assume it is only after the bold ones that you want to use in working out your E column?

Yes, "after" is key word.
The "bold ones" (in column C - SUM column) are those equal or more than "required level" , number ten (10) in cell D2 of my example (which of course I choose arbitrarily).

 

[mburrows]

This code should work. Will need to be run each time any data is added/changed:

Sub Results()

Dim Level As Double
Dim i As Integer
Dim j As Integer

Level = Range("D2").Value
j = 2

For i = 2 To Range("C2").End(xlDown).Row
    If Cells(i, 3) >= Level Then
        Cells(j, 5).Value = Cells(i + 1, 2).Value + Cells(i + 2, 2).Value + Cells(i + 3, 2).Value
        j = j + 1
    End If
Next i

End Sub