Add sequences of positive then negative numbers

  • Thread starter Thread starter judoist
  • Start date Start date
J

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 ?
 
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
 
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 said:
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]...
Click to expand...


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

Any ideas ?
 
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
 
Back
Top