Results 1 to 5 of 5

Thread: Running Total reset by negative value

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Running Total reset by negative value

    I am trying to setup a spreadsheet to track a number of values. I only need to create a running total when the total of 2 columns becomes a positive value and then track it back down to when it moves back to a negative. The data looks like this:

    Value A Value B A + B Required Result
    0 -2000 -2000 0
    0 -3500 -3500 0
    41000 -11000 30000 30000
    0 -1600 -1600 28400
    0 -1800 -1800 26600
    0 -11800 -11800 14800
    0 -6000 -6000 8800
    0 -10000 -10000 -1200
    0 -5000 -5000 0
    20000 -5000 15000 15000
    0 -8000 -8000 7000
    0 -5000 -5000 2000
    0 -6500 -6500 -4500
    0 -3300 -3300 0
    0 -9800 -9800 0
    0 -5300 -5300 0
    I feel like there is an easy solution that I am just missing by nesting IF loops or something along those lines, I have tried with extra columns but I am missing something. Creating a running total isn't hard but having it reset is the problem.

    Any ideas are gratefully received!

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,243
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Running Total reset by negsative value

    Try this

     ABCD
    1Value AValue BA + BRequired Result
    20-2000-20000
    30-3500-35000
    441000-110003000030000
    50-1600-160028400
    60-1800-180026600
    70-11800-1180014800
    80-6000-60008800
    90-10000-10000-1200
    100-5000-50000
    1120000-50001500015000
    120-8000-80007000
    130-5000-50002000
    140-6500-6500-4500
    150-3300-33000
    160-9800-98000
    170-5300-53000

    CellFormula
    D2=IF(AND(IFERROR(VALUE(D1),0)=0,C2<0),0,IF(D1>=0,D1+C2,0))
    Regards Dante Amor

  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,243
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Running Total reset by negsative value

    Edit:

    Use this formula:

    =IF(AND(IFERROR(VALUE(D1),0)=0,C2<0),0,IF(IFERROR(VALUE(D1),0)>=0,IFERROR(VALUE(D1),0)+C2,0))
    Regards Dante Amor

  4. #4
    New Member
    Join Date
    Jun 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Running Total reset by negsative value

    And just like that, problem solved. Thanks, I was coming at it from the wrong angle!

  5. #5
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,243
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Running Total reset by negsative value

    I'm glad to help you. Thanks for the feedback.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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