Add sequences of positive then negative numbers

[judoist]

I have a long list of random positive and negative numbers in column A.
Id like to be able to add sequences of positive then negative numbers
as they occur.

For example... in the list

5,-1,-5,-3,2,8,-1,-3,9,7,5,11,-5,6,5

... i would add the positive numbers until the next number in the list
was negative, then add the negative numbers till the next number was
positive, etc.

The answers for the above list would be 5, -9, 10 , -4, 21, -5, 11

Any ideas ?

 

[duane]

this should get you going. If you need help in writing the results let
me know
I assume your column of data is column A

I wrote sums in column C

Sub macro1()
startrow = Cells(1, 1).End(xlDown).Row
endrow = Cells(10000, 1).End(xlUp).Row
Dim subtotal(100) As Double
j = 1
subtotal(j) = Cells(startrow, 1).Value
If Cells(startrow, 1).Value >= 0 Then h = 1 Else h = -1
For i = startrow + 1 To endrow
If Cells(i, 1).Value >= 0 And Cells(i - 1, 1).Value >= 0 Then GoTo add
_
Else If Cells(i, 1).Value < 0 And Cells(i - 1, 1).Value < 0 Then GoTo
add _
Else j = j + 1
subtotal(j) = Cells(i, 1).Value
GoTo nexti
add:
subtotal(j) = subtotal(j) + Cells(i, 1).Value
nexti:
Next i
For k = 1 To j
Cells(k + startrow - 1, 3) = subtotal(k)
Next k
End Sub

 

[Niek Otten]

It seems to me there is a little error in your example: the list should be
5,-9,10,-4,32,11 shouldn't it?

You got a VBA solution; fine. If you need something else, post again, and
I'll give a worksheet formula solution

 

[Niek Otten]

< If you need something else, post again, >

This same thread please

 

[judoist]

It seems to me there is a little error in your example: the list should
be
5,-9,10,-4,32,11 shouldn't it?

You got a VBA solution; fine. If you need something else, post again,
and
I'll give a worksheet formula solution

--
Kind regards,

Niek Otten

in
message news:[email protected]...

Youre right, i was actually hoping for a worksheet example.

Any ideas ?

 

[Niek Otten]

Suppose your data is in A1 and below
In B1: =A1
In B2: =IF(SIGN(A1)=SIGN(A2),B1+A2,A2
Copy down as far as needed
In C1: =IF(SIGN(A1)=SIGN(A2),"",B1)
Copy down as far as needed

 

[judoist]

Fantastic.

Thanks Niek