Suming numbers until a positive number is reached

cjw6886

New Member
Joined
Nov 5, 2016
Messages
2
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

New Member
Joined
Jan 9, 2015
Messages
39

<colgroup><col style="width:48pt" width="64" span="8"> </colgroup><tbody>


</tbody>
200
400-100-300500600-200700
200
400002006000500

<tbody>
</tbody>

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

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

Well-known Member
Joined
Dec 2, 2002
Messages
1,938
Office Version
2019
Platform
Windows
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

Well-known Member
Joined
Jan 17, 2015
Messages
873
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))
 

Forum statistics

Threads
1,082,441
Messages
5,365,539
Members
400,837
Latest member
ELMST616

Some videos you may like

This Week's Hot Topics

Top