Add sequences of positive then negative numbers
 

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 ?


--
judoist
------------------------------------------------------------------------
judoist's Profile: http://www.excelforum.com/member.php...o&userid=10595
View this thread: http://www.excelforum.com/showthread...hreadid=487976

Add sequences of positive then negative numbers
 

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


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=487976

Add sequences of positive then negative numbers
 

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

"judoist" wrote in
message ...

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 ?


--
judoist
------------------------------------------------------------------------
judoist's Profile:
http://www.excelforum.com/member.php...o&userid=10595
View this thread: http://www.excelforum.com/showthread...hreadid=487976

Add sequences of positive then negative numbers
 

< If you need something else, post again,

This same thread please

--
Kind regards,

Niek Otten


"Niek Otten" wrote in message
...
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

"judoist" wrote in
message ...

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 ?


--
judoist
------------------------------------------------------------------------
judoist's Profile:
http://www.excelforum.com/member.php...o&userid=10595
View this thread:
http://www.excelforum.com/showthread...hreadid=487976

Add sequences of positive then negative numbers
 

Niek Otten Wrote:
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

"judoist" wrote
in
message ...

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 ?


--
judoist

------------------------------------------------------------------------
judoist's Profile:
http://www.excelforum.com/member.php...o&userid=10595
View this thread:
http://www.excelforum.com/showthread...hreadid=487976



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

Any ideas ?


--
judoist
------------------------------------------------------------------------
judoist's Profile: http://www.excelforum.com/member.php...o&userid=10595
View this thread: http://www.excelforum.com/showthread...hreadid=487976

Add sequences of positive then negative numbers
 

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

--
Kind regards,

Niek Otten

"judoist" wrote in
message ...

Niek Otten Wrote:
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

"judoist" wrote
in
message ...

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 ?


--
judoist

------------------------------------------------------------------------
judoist's Profile:
http://www.excelforum.com/member.php...o&userid=10595
View this thread:
http://www.excelforum.com/showthread...hreadid=487976



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

Any ideas ?


--
judoist
------------------------------------------------------------------------
judoist's Profile:
http://www.excelforum.com/member.php...o&userid=10595
View this thread: http://www.excelforum.com/showthread...hreadid=487976

Add sequences of positive then negative numbers
 

Fantastic.

Thanks Niek


--
judoist
------------------------------------------------------------------------
judoist's Profile: http://www.excelforum.com/member.php...o&userid=10595
View this thread: http://www.excelforum.com/showthread...hreadid=487976