Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
judoist
 
Posts: n/a
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.misc
duane
 
Posts: n/a
Default 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

  #3   Report Post  
Posted to microsoft.public.excel.misc
Niek Otten
 
Posts: n/a
Default 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



  #4   Report Post  
Posted to microsoft.public.excel.misc
Niek Otten
 
Posts: n/a
Default 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





  #5   Report Post  
Posted to microsoft.public.excel.misc
judoist
 
Posts: n/a
Default 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



  #6   Report Post  
Posted to microsoft.public.excel.misc
Niek Otten
 
Posts: n/a
Default 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



  #7   Report Post  
Posted to microsoft.public.excel.misc
judoist
 
Posts: n/a
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula to make Negative Values Positive & Positive Values Negative? mustard Excel Discussion (Misc queries) 4 September 26th 05 10:05 PM
How to make negative numbers look like 0 Trisha V via OfficeKB.com Excel Discussion (Misc queries) 12 August 5th 05 07:12 PM
How do I show negative numbers on a stacked graph JMZ Charts and Charting in Excel 3 February 26th 05 04:21 PM
adding only positive numbers Jacob Excel Discussion (Misc queries) 2 November 30th 04 01:24 AM
Negative Numbers Glenda Excel Discussion (Misc queries) 3 November 26th 04 03:06 PM


All times are GMT +1. The time now is 03:35 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"