Announcement

Collapse
No announcement yet.

$30 USD - Find a formula for running total until reaching a mark and then restart

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • $30 USD - Find a formula for running total until reaching a mark and then restart



    Hello, I am looking for an excel formula that does the following:

    We have a mark to reach, let's say MARK=12

    In column A we have "random" amounts.
    In column B, I want a formula that:
    - Calculates the running amount in col A and
    - If the running total has reached MARK, then display that running total, otherwise leave cell empty and THEN
    - next cell repeats the process but ignoring the cells that were part of a running total previously displayed.

    Example:
    MARK = 12
    Col A Col B
    7
    4
    5 16
    1
    6
    5 12
    6
    8 14
    4
    3
    4
    8 19
    ... and so on
    Can you do this?
    Thank you,
    Celia

  • #2
    Try this copied down (example shown in columns F and G):

    Excel 2016 (Windows) 32 bit
    1
    MARK
    12
    2
    3
    7
    4
    4
    5
    5
    16
    6
    1
    7
    6
    8
    5
    12
    9
    6
    10
    8
    14
    11
    4
    12
    3
    13
    4
    14
    8
    19
    Sheet: Sheet1
    Excel 2016 (Windows) 32 bit
    3
    =IF(SUM(F$2:F3)-SUM(G$2:G2)<G$1,"",SUM(F$2:F3)-SUM(G$2:G2))
    Sheet: Sheet1
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    Comment


    • #3
      Hi, Ali! Thank you so much for replying.

      This seems to work, If I put the mark in G1, right?
      Is there a way this could work with the mark value put anywhere else in the workbook?
      Thank you,
      Celia

      Comment


      • #4
        Yes - put it wherever you like and then change the reference to G$1 to whichever cell you have chosen. So, let's assume you choose K43, your formula will be:

        =IF(SUM(F$2:F3)-SUM(G$2:G2)<K$43,"",SUM(F$2:F3)-SUM(G$2:G2))
        Ali

        Enthusiastic self-taught user of MS Excel who's always learning!
        If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

        Comment


        • #5
          Never mind, Ali! All is fine. You saved my day! How can I pay you? (I am not sure if there is a way to communicate in private here.)

          Comment


          • #6
            You know, I have no idea how it works!!! I have never answered a Hire Help question before, in fact I didn't even realise this was one until your duplicate thread appeared. I don't do this for money - I am happy to waive the fee. Buy yourself something nice instead.
            Ali

            Enthusiastic self-taught user of MS Excel who's always learning!
            If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

            Comment


            • #7
              Ali, now you made my day twice!
              Thank you so much! I just joined today. I feel like I should come back to help others too!

              Comment


              • #8


                I would recommend that you don't post in the hire help section - it's unlikely from what you have posted today that you will need to, so stick to the free areas. I'm just glad to have helped.
                Ali

                Enthusiastic self-taught user of MS Excel who's always learning!
                If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

                Comment

                Working...
                X