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?action=getinfo&userid=10595
View this thread: http://www.excelforum.com/showthread.php?threadid=487976

0
11/24/2005 4:29:56 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
630 Views

Similar Articles

[PageSpeed] 10

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?action=getinfo&userid=11624
View this thread: http://www.excelforum.com/showthread.php?threadid=487976

0
11/24/2005 4:58:01 PM
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" <judoist.1z0evy_1132849800.9663@excelforum-nospam.com> wrote in 
message news:judoist.1z0evy_1132849800.9663@excelforum-nospam.com...
>
> 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?action=getinfo&userid=10595
> View this thread: http://www.excelforum.com/showthread.php?threadid=487976
> 


0
nicolaus (2022)
11/24/2005 5:07:46 PM
< If you need something else, post again, >

This same thread please

-- 
Kind regards,

Niek Otten


"Niek Otten" <nicolaus@xs4all.nl> wrote in message 
news:%23WfAVmR8FHA.2364@TK2MSFTNGP12.phx.gbl...
> 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" <judoist.1z0evy_1132849800.9663@excelforum-nospam.com> wrote in 
> message news:judoist.1z0evy_1132849800.9663@excelforum-nospam.com...
>>
>> 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?action=getinfo&userid=10595
>> View this thread: 
>> http://www.excelforum.com/showthread.php?threadid=487976
>>
>
> 


0
nicolaus (2022)
11/24/2005 5:10:05 PM
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" <judoist.1z0evy_1132849800.9663@excelforum-nospam.com> wrote
> in
> message news:judoist.1z0evy_1132849800.9663@excelforum-nospam.com...
> >
> > 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?action=getinfo&userid=10595
> > View this thread:
> http://www.excelforum.com/showthread.php?threadid=487976
> >


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

Any ideas ?


-- 
judoist
------------------------------------------------------------------------
judoist's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=10595
View this thread: http://www.excelforum.com/showthread.php?threadid=487976

0
11/25/2005 12:18:17 AM
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" <judoist.1z10na_1132878001.0107@excelforum-nospam.com> wrote in 
message news:judoist.1z10na_1132878001.0107@excelforum-nospam.com...
>
> 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" <judoist.1z0evy_1132849800.9663@excelforum-nospam.com> wrote
>> in
>> message news:judoist.1z0evy_1132849800.9663@excelforum-nospam.com...
>> >
>> > 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?action=getinfo&userid=10595
>> > View this thread:
>> http://www.excelforum.com/showthread.php?threadid=487976
>> >
>
>
> Youre right, i was actually hoping for a worksheet example.
>
> Any ideas ?
>
>
> -- 
> judoist
> ------------------------------------------------------------------------
> judoist's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=10595
> View this thread: http://www.excelforum.com/showthread.php?threadid=487976
> 


0
nicolaus (2022)
11/25/2005 1:00:35 PM
Fantastic.

Thanks Niek


-- 
judoist
------------------------------------------------------------------------
judoist's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=10595
View this thread: http://www.excelforum.com/showthread.php?threadid=487976

0
11/26/2005 5:51:04 AM
Reply:

Similar Artilces:

Why do my pie charts have labels which say "add text"?
Using Excel 2007 and my template comes up with this add text label for multiple labels on the pie chart. Even when I save a new template it still happens. Why? ...

skipping check numbers
Hello: I think I know what the solution is here. But, I want to double-check my thinking with you all. The client has a check format with stub on top and bottom. When they print checks on the fly, the vouchers print twice on the stubs. I found a TechKnowledge on PartnerSource which says to delete the Payment Stub Duplicate File table (PM10801). Fine. Now, I have not had a chance to go onsite to help them yet. But, just yesterday they called and said that they printed several checks on the fly in a batch, posted the batch, and not only did the duplicate voucher data appear on the s...

Is it possible to add background sound to an email in Outlook 2007?
Is it possible to add background sound to an email in Outlook 2007? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/ms-outlook/201005/1 Outlook can't play active content so while you could embed it (using an HTML editor), it won't play. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: mailto:EMO-NEWSLETTER-S...

Count down slide number
Is there a way to automatically generate a countdown slide counter as a footer on a presentation? I have been doing it manually to help me within time on a presentation. It sure would be a good feature to include in the program, IMHO. PowerPoint 2007 Terry, I am not sure what you mean by, “count down slide number”. If you mean slide numbers here’s how to do it:- 1. Open a new Presentation. 2. Insert tab / Text group / Header & Footer / Header and Footer window should launch / Slide tab / place a tick (check) in the Slide number field (towards the lower left hand...

remove spaces between numbers
I have about 8000 numbers (1 digit numbers) in four columns in MS Word. The numbers have spaces between them but I dont want the spaces. Is there an easy way to remove these spaces without having to do it by hand one by hand? Use the replace function - see http://www.gmayor.com/replace_using_wildcards.htm Won't this produce an 8000 digit number? -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <&g...

Sequential numbering of invoices
I have recently downloaded an Excel template for invoices from Microsoft's website. Is there any way to fix it so that every time I create a new document based on this template, it automatically gives it an invoice number one greater than the previous invoice (eg, starting at 100 and then the next one I open would be 101, then 102 and so on)? http://www.mcgimpsey.com/excel/udfs/sequentialnums.html -- Regards, Peo Sjoblom "Nick Xylas" <nickxylas@wmconnect.com> wrote in message news:1180551140.693535.260930@g4g2000hsf.googlegroups.com... >I have recently download...

Displaying a negative time
Hi There I've created a time sheet in excel for my work. One of my colleagues however owes the organisation some hours. Sadly this is right at the beginning of the year so I am having a bit of a problme setting it up to display this. She owes the organisation 7hrs 45mins. I tried entering into the cell 30/12/1903 16:15:00 however this then just displays that entry and not -7:45:00 Is there any way I can get this to work? Cheers Colin Change to 1904 date system Tools>Options> Calculation>1904 date system HTH Bob "Colin Weir" <colin...

goal seek and negative values
im using goal seek and from time to time it returns a negative value. how do i prevent this? i have set validation rules for the cells so that i cannot manually enter a negative value but goal seek seems to igore this. Steve, Use Solver instead. Put in a constraint: A2>=0. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "steve" <steve@discussions.microsoft.com> wrote in message news:E5787E67-D3C9-4679-A514-8C26B9A75E48@microsoft.com... > im using goal seek and from time to time it returns a negative value. ...

Numbers are changed in Excel?
I created a spread sheet of some stuff. I entered some serial and account numbers. For some reason that everyone but me probably knows the numbers are changed by Excel by inserting a ., a + sign, etc. hat are not as I typed. What's going on here? Thanks. I would say it's some formatting (either default or pre-existing) in those cells. Try giving all those cells the "Text" format. Then Excel won't add decimal places or signs. I -think- Excel won't let you do math with those values (unless you convert them first), but you probably don't need to add or subtra...

How to add Opportunity link to workflow email
Hello, how could I add link to Opportunity, when sending email from workflow. Example: I reassign Opportunity to other user and workflow sends email to him and informs about new opportunity assigned to him. How to add hyperlink to email ??? I use MS CRM 4.0 B.I. On 2 Lut, 13:07, "B.I." <aid...@jdjdjd.lt> wrote: > Hello, > > how could I add link to Opportunity, when sending email from workflow. > > Example: > I reassign Opportunity to other user and workflow sends email to him > and informs about new opportunity assigned to him. > How to add...

Add two Columns to a table from another table
It has been a couple of months since I have worked with access and I can't remeber the right way to put the critera. I want to add two columns from table A to table B where the machine name is equal to each other. I know I have to use an append query I just can't figuer out how to set the critera to place the correct data in the right place. Both tables have been imported and both have a column called machine name. I want to add two columns from table A to table B where the machine names are the same. Please help me out. Thanks very much, Greg It isn't clear whether you ar...

How to change the file associated with Windows Media Player Add-on
Hello, My home computer is not playing streaming video correctly, while my office computer can. I compare their settings and find one difference between them. At home, the file associated with Windows Media Player under the Manage Add-on dialog box is msdxm.ocx. At work, the file is wmpdxm.dll. I want to change the home computer setting to be the same as that for office computer. How can I change the file to point to wmpdxm.dll? Thanks in advance. Herbert Try microsoft.public.windowsmedia.player newsgroup. Herbert Chan wrote: > Hello, > > My home c...

Excel and consecutive number
Is there a way to have excel locate a set number of consecutive non-zero numbers and give me an average of those numbers? trying to get around this problem: 5 largest consecutive non-zero numbers of the last 10 non-zero terms hope there is some help out there for me If I understand what you want correctly, here is one method. Assuming you have a list of numbers in column A with the first number in row 2: Enter this formula in B2 and copy down through all rows of data: =IF(A3=0,0,IF(B2=10,10,B2+1)) This formula counts consectuive non-zero numbers in column A. Enter this form...

Add New User but no Mailbox created
To anyone be able to assist, We are running Windows Small Business Server 2003. I have added a user in Active Directory Users and Computers. It is possible to log onto the Domain as the new user. My problems are that:- 1. When the new user starts Outlook the Outlook Setup Wizard starts. The new user enters their Mailbox name and presses "Check Name". Outlook advises:- "The name could not be resolved. The name could not be matched to a name in the address list." also 2. The new user does not appear in the "Global Address List". If however I check the &quo...

adding largest numbers
I want to add the largest five numbers in a range, which will change daily. I have put a condition on to highlight the top five and that is working. I don't know how to add the largest five numbers since the largest 5 numbers will change from cell to cell daily. I am assuming you would use a SUMIF, then range, but how do you write a critieria for the largest five numbers? Thanks for helping a beginner. Try this, =SUMPRODUCT(LARGE(A1:A100,{1,2,3,4,5})) HTH Regards, Howard "nrbrtsn" <nrbrtsn@discussions.microsoft.com> wrote in message news:5E0744A0-...

Setting a limit to the number of characters on an Edit box..
How do you set a character limit on an edit box? Use CEdit::SetLimitText: http://msdn2.microsoft.com/en-us/library/c4db48kc.aspx -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com <kefkastudio@gmail.com> wrote in message news:1157049125.043635.81750@m73g2000cwd.googlegroups.com... > How do you set a character limit on an edit box? > You can do this using SetLimitText() in CEdit. http://msdn2.microsoft.com/en-us/library/75bh1f1t.aspx Tom <kefkastudio@gmail.com> wrote in message news:1157049125.043635.81750@m73g2000cwd.googlegroups.com... > How do you set a characte...

Add a VOID status against voided purchase orders
When voiding a Purchase Order the status of the PO remains as NEW - this is for the PO header and lines. It would be useful to have the PO status changed to VOID for these PO's. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolut...

number rounding in MS Excel
Hi, This may have been answered many times. In MS EXCEL is there a way that it calculates all numbers to 2 decimal digit accuracy instead of the 15 digit default? Thanks in advance for the answer. Hi You can set your decimal places in your cells as 2 and then check Precision As Displayed on the Tools / Options / Calculation page. Be careful though, it means what it says!! -- Andy. "mahusain" <abidh@bdnet.net> wrote in message news:ab59c6f6.0404200315.5196e8aa@posting.google.com... > Hi, > > This may have been answered many times. > > In MS EXCEL is ther...

Add members to DL
Is there any method or tools to add mass members to the DL from outlook 2000 (client)? Thanks a lot. ...

Calendar Add-In
Anyone know of a FREE calendar add-in that I when input appointments etc some kind of notice pops up when the date selected comes around? It's not hard to make this as an Excel file with functions (IF and TODAY etc) and perhaps a bit of Conditional Formatting, but I want something that I can use no matter what file I am working with. (If the VBA is not password locked, that's even better, but I'll settle for just the add-in. I should could use one) Appreciate any feedback...thanks. --- Message posted from http://www.ExcelForum.com/ If you have Office, then use Outlook's c...

Excel 2000
I have been using the Excel Program for several years (home use) but not to a 1/10th of its potential. I want to create an invoice for my business whereby the invoice number automatically prints in sequencial order every time I ask it to print and to remember the last number which was printed - if that makes any sense. The invoice will be a template and not completed "on-screen". Is this possible?:confused: --- Message posted from http://www.ExcelForum.com/ I think it gets too dangerous to mechanize this a lot. If sheets are lost and have to be reprinted, you could have more...

Negative to positive value
How to change negative value to positive? For example i text box value is in -4 then i want to change it into 4... How? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200707/1 On Thu, 05 Jul 2007 05:16:14 GMT, "EMILYTAN via AccessMonster.com" <u33296@uwe> wrote: >How to change negative value to positive? > >For example i text box value is in -4 then i want to change it into 4... >How? Multiply by -1, or use the Abs() function. What's the context? When do you want this to happen? John W. Vins...

How do I limit number of fonts available in Word
I only switch between a handful of fonts, when I work with Office products. How do I limit the number of fonts listed? I've removed a lot from c: > Windows > Fonts, but every time I open Word, I'm still seeing more than I'm interested in. Install file management software. Word displays all the installed fonts the current printer is capable of printing. Cheaper still format your documents with styles and ignore the font listing altogether. -- <>>< ><<> ><<> <>>< ><<> <>>< <>><...

Limit on number of users for a shared workbook?
Win2k Office XP Is there a known limit on the number of users working simultaneously on a shared workbook? I'm aware of the conflict resolution scenario, I'm just curious to know if (according to MS) there's a physical limit, or a point at which performance would degrade to an "unacceptable" level. Thanks, Rob ...

HELP
We have an application that is used by over 8000 people worldwide. One of our users is just starting to have the following problem: Run-time error '-2147467259(80004005)': Method 'Add' of object 'CommandBarControls' failed I have read that this problem can be caused by trying to open the file in Internet Explorer. The user has told me that she has tried opening MS Excel and then the .xls file and she still gets the same problem when the file is trying to open. She is the only one that has had this problem. Could there be something wrong with her Excel settings or som...