+ Reply to Thread
Results 1 to 17 of 17

Sum Negatives into next Positive

  1. 11-04-2010, 04:21 PM #1
    ScooterNM5
    ScooterNM5 is offline
    Registered User
    Join Date
    11-04-2010
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2003
    Posts
    9

    Sum Negatives into next Positive

    Hi all,

    I'm stumped and hoping you could help. I have a column that might look like this:

    -5
    4
    -9
    -7
    6
    -5
    7

    The numbers above are in Column B. What I would like to do is start with the first number (B2) and add it into the next positive, displaying the result in Column C in the same row as the positive number. It got tricky for me because sometimes there are more that one negative in a row. So, in the end, the above example would look like this:

    -5 |
    4 | -1
    -9 |
    -7 |
    6 | 10
    -5 |
    7 | 2


    Basically, after every positive it would display the result and start over.

    Thank you in advance, I appreciate any help I can get!

    Scott
    Last edited by ScooterNM5; 11-04-2010 at 04:40 PM.
    Reply With QuoteRegister To Reply
  2. 11-04-2010, 04:48 PM #2
    KRBeckman
    KRBeckman is offline
    Registered User
    Join Date
    01-08-2010
    Location
    Michigan, US
    MS-Off Ver
    Excel 2007, Outlook 2007
    Posts
    15

    Re: Sum Negatives into next Positive

    Quote Originally Posted by ScooterNM5 View Post
    Hi all,

    I'm stumped and hoping you could help. I have a column that might look like this:

    -5
    4
    -9
    -7
    6
    -5
    7

    The numbers above are in Column B. What I would like to do is start with the first number (B2) and add it into the next positive, displaying the result in Column C in the same row as the positive number. It got tricky for me because sometimes there are more that one negative in a row. So, in the end, the above example would look like this:

    -5 |
    4 | -1
    -9 |
    -7 |
    6 | 10
    -5 |
    7 | 2


    Basically, after every positive it would display the result and start over.

    Thank you in advance, I appreciate any help I can get!

    Scott
    This can be done either with VBA or with formulas... with formulas being the easier option.

    I've attached a spreadsheet that I believe accomplishes what you're trying to do (run "test" macro if you wish to use the macro solution). The only problem I see is the "10" you have next to the "6"... if I'm not missing what you're trying to do I think the "10" needs to be a "-1", as 6 + -7 = -1. If I'm wrong try explaining it again, possibly with more examples.

    Hope this helps!
    Attached Files Attached Files
    Last edited by KRBeckman; 11-04-2010 at 05:18 PM. Reason: Forgot to mention name of macro & Updated code per request
    Reply With QuoteRegister To Reply
  3. 11-04-2010, 04:52 PM #3
    ScooterNM5
    ScooterNM5 is offline
    Registered User
    Join Date
    11-04-2010
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Sum Negatives into next Positive

    So you are close...the 10 comes from -9 + -7 + 6....then start over....-5+7.....then start over...

    Does that make sense? I really appreciate it!
    Reply With QuoteRegister To Reply
  4. 11-04-2010, 04:59 PM #4
    KRBeckman
    KRBeckman is offline
    Registered User
    Join Date
    01-08-2010
    Location
    Michigan, US
    MS-Off Ver
    Excel 2007, Outlook 2007
    Posts
    15

    Re: Sum Negatives into next Positive

    Quote Originally Posted by ScooterNM5 View Post
    So you are close...the 10 comes from -9 + -7 + 6....then start over....-5+7.....then start over...

    Does that make sense? I really appreciate it!
    I think I get it... but now I'm thinking the "10" should be a "-10", correct?
    Reply With QuoteRegister To Reply
  5. 11-04-2010, 05:07 PM #5
    ScooterNM5
    ScooterNM5 is offline
    Registered User
    Join Date
    11-04-2010
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Sum Negatives into next Positive

    HAHA, yes sorry. This thing has my brain fried!
    Reply With QuoteRegister To Reply
  6. 11-04-2010, 05:12 PM #6
    KRBeckman
    KRBeckman is offline
    Registered User
    Join Date
    01-08-2010
    Location
    Michigan, US
    MS-Off Ver
    Excel 2007, Outlook 2007
    Posts
    15

    Re: Sum Negatives into next Positive

    Quote Originally Posted by ScooterNM5 View Post
    HAHA, yes sorry. This thing has my brain fried!
    No worries... attached is the updated spreadsheet... I didn't take the time to figure out the formula version, just the VBA. Also, this assumes you want it to perform the addition if the value in column "B" is 0. If you don't want it to do this, just change the "< 0" in the code to "<= 0" and you should be all set.

    Let me know how it works, I edited my original post to include the updated spreadsheet.
    Reply With QuoteRegister To Reply
  7. 11-04-2010, 05:17 PM #7
    KRBeckman
    KRBeckman is offline
    Registered User
    Join Date
    01-08-2010
    Location
    Michigan, US
    MS-Off Ver
    Excel 2007, Outlook 2007
    Posts
    15

    Re: Sum Negatives into next Positive

    Just found another bug... spreadsheet from my original post updated.
    Reply With QuoteRegister To Reply
  8. 11-04-2010, 05:23 PM #8
    ScooterNM5
    ScooterNM5 is offline
    Registered User
    Join Date
    11-04-2010
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Sum Negatives into next Positive

    I can't thank you enough. This works perfectly!!!

    Scott
    Reply With QuoteRegister To Reply
  9. 11-04-2010, 05:26 PM #9
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Sum Negatives into next Positive

    A formula alternative:

    Please Login or Register  to view this content.
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  10. 11-04-2010, 05:26 PM #10
    KRBeckman
    KRBeckman is offline
    Registered User
    Join Date
    01-08-2010
    Location
    Michigan, US
    MS-Off Ver
    Excel 2007, Outlook 2007
    Posts
    15

    Re: Sum Negatives into next Positive

    Quote Originally Posted by ScooterNM5 View Post
    I can't thank you enough. This works perfectly!!!

    Scott
    No problem, glad to help... just add to my Reputation if you haven't already.
    Reply With QuoteRegister To Reply
  11. 11-04-2010, 05:27 PM #11
    ScooterNM5
    ScooterNM5 is offline
    Registered User
    Join Date
    11-04-2010
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Sum Negatives into next Positive

    Sorry, it works; but one question (I'm sure a dumb one).

    How does the below work:

    If Cells(r, 2).Value < 0 Then
    y = r + 1
    Specifically, what does 'y' do? This is the only place it is referenced.
    Reply With QuoteRegister To Reply
  12. 11-04-2010, 05:31 PM #12
    KRBeckman
    KRBeckman is offline
    Registered User
    Join Date
    01-08-2010
    Location
    Michigan, US
    MS-Off Ver
    Excel 2007, Outlook 2007
    Posts
    15

    Re: Sum Negatives into next Positive

    Quote Originally Posted by shg View Post
    A formula alternative:

    Please Login or Register  to view this content.
    This isn't working for me... I'm really interested in how this work though, can you attach your example?
    Reply With QuoteRegister To Reply
  13. 11-04-2010, 05:31 PM #13
    ScooterNM5
    ScooterNM5 is offline
    Registered User
    Join Date
    11-04-2010
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Sum Negatives into next Positive

    Thanks SHG!
    Reply With QuoteRegister To Reply
  14. 11-04-2010, 05:32 PM #14
    KRBeckman
    KRBeckman is offline
    Registered User
    Join Date
    01-08-2010
    Location
    Michigan, US
    MS-Off Ver
    Excel 2007, Outlook 2007
    Posts
    15

    Re: Sum Negatives into next Positive

    Quote Originally Posted by ScooterNM5 View Post
    Sorry, it works; but one question (I'm sure a dumb one).

    How does the below work:



    Specifically, what does 'y' do? This is the only place it is referenced.
    yea, I noticed that too, which is why I updated my post again... sorry I'm not very good at proffing my code.
    Reply With QuoteRegister To Reply
  15. 11-04-2010, 05:39 PM #15
    ScooterNM5
    ScooterNM5 is offline
    Registered User
    Join Date
    11-04-2010
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Sum Negatives into next Positive

    Quote Originally Posted by KRBeckman View Post
    This isn't working for me... I'm really interested in how this work though, can you attach your example?
    Here is an example of SHG's solution.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  16. 11-04-2010, 05:42 PM #16
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Sum Negatives into next Positive

    can you attach your example?
    Sure ...
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  17. 11-05-2010, 07:01 AM #17
    KRBeckman
    KRBeckman is offline
    Registered User
    Join Date
    01-08-2010
    Location
    Michigan, US
    MS-Off Ver
    Excel 2007, Outlook 2007
    Posts
    15

    Re: Sum Negatives into next Positive

    Quote Originally Posted by shg View Post
    Sure ...

    Weird, not really sure what I was doing wrong... anyway, glad it works.

    On another note, I can see the advantages of both methods... the macro method requires that macro to be ran everytime you want the values in column "C" to be updated, but if the number of rows your data occupies in column "B" varies, you won't be required to re-size the range containing the formula or fill the formula in column "C" high enough row so that it covers the data you put into column "B".

    Also I updated the spreadsheet so that if the number of items in column "B" decreases it erases the number in the respective cell in column "C".
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1