Suming numbers until a positive number is reached

[cjw6886]

Hello,

I need a formula to reach the output below. I need to have the cells sum the numbers before it that are negative and have it stop once it reaches a positive number. Also, if the number is negative I need it to end up as 0. Thanks in advance for any contribution you make to helping me get this figured out!


Data
200 400 -100 -300 500 600 -200 700

Output
200 400 0 0 100 600 0 500

 

[shreif]

[TABLE="class: grid, width: 512"]
<colgroup><col style="width:48pt" width="64" span="8"> </colgroup><tbody>

[TR]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 512"]
<tbody>[TR]
[TD="width: 64, align: right"]200
[/TD]
[TD="width: 64, align: right"]400[/TD]
[TD="width: 64, align: right"]-100[/TD]
[TD="width: 64, align: right"]-300[/TD]
[TD="width: 64, align: right"]500[/TD]
[TD="width: 64, align: right"]600[/TD]
[TD="width: 64, align: right"]-200[/TD]
[TD="width: 64, align: right"]700[/TD]
[/TR]
[TR]
[TD="align: right"]200
[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]500[/TD]
[/TR]
</tbody>[/TABLE]

highlight select your target range manually , then run the below code,

Sub Shreif11Nov2016()
    Dim rng, rng1 As Range
    Dim i, c As Long
    For Each rng In Selection
        If rng > 0 Then
            rng.Offset(1) = rng
        Else
            rng.Offset(1) = 0
      End If
    Next
    Selection.Offset(1).Select
    For Each rng1 In Selection
        If i < 0 And rng1 <> 0 Then
            rng1 = rng1 + c
            i = 0
            c = 0
        End If
        If rng1 = 0 Then
            i = rng1.Offset(-1)
            c = c + i
        End If
    Next
End Sub

 

[bosco_yip]

Try,

1] Left A1 blank, your data housed in B1:I1

2] In B2, formula copy across :

=IF(B1>0,IF(A1<0,MIN(A1+B1,B1),B1),0)

Regards

 

[admiral100]

Hi,

Output as your example :

1] Left A1 blank, your data housed in B1:I1

2] In B2, formula copy across :

=IF(B1<0,0,IF(A1<0,SUM($B$1:B1)-SUM(A$2:$B2),B1))

 

[cjw6886]

Thanks Admiral! That is exactly what i needed

 

[admiral100]

you're welcome