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, 32 -5, 11
Any ideas ?
Last edited by judoist; 11-24-2005 at 12:33 PM.
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
not a professional, just trying to assist.....
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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
< If you need something else, post again, >
This same thread please
--
Kind regards,
Niek Otten
"Niek Otten" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in
> message news:[email protected]...
>>
>> 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
>>
>
>
Originally Posted by Niek Otten
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
--
Kind regards,
Niek Otten
"judoist" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote
>> in
>> message news:[email protected]...
>> >
>> > 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
>
Fantastic.
Thanks Niek
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks