| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula to make Negative Values Positive & Positive Values Negative? | Excel Discussion (Misc queries) | |||
| How to make negative numbers look like 0 | Excel Discussion (Misc queries) | |||
| How do I show negative numbers on a stacked graph | Charts and Charting in Excel | |||
| adding only positive numbers | Excel Discussion (Misc queries) | |||
| Negative Numbers | Excel Discussion (Misc queries) | |||
Hybrid Mode
