kpcbloom

Q: Transfer a running total to a new cell.  A computed running total in one column transfered to a cell in the next column

In my numbers spread sheet I have a column that calculates totals for two columns and then as a running total.  I need to move the running total computation to a cell in the next column.  I'm not sure how to write a formula to capture a "moving cell" as the running total changes location as more entries are made.  I'm sure this is a no brainer for someone more experienced.

MacBook Air, Mac OS X (10.6.8)

Posted on Sep 14, 2016 1:26 PM

Close

Q: Transfer a running total to a new cell.  A computed running total in one column transfered to a cell in the next column

  • All replies
  • Helpful answers

  • by Yellowbox,

    Yellowbox Yellowbox Sep 15, 2016 4:29 AM in response to kpcbloom
    Level 6 (10,515 points)
    Mac OS X
    Sep 15, 2016 4:29 AM in response to kpcbloom

    Hi kpcbloom,

    I'm sure this is a no brainer for someone more experienced.

    Welcome to Numbers for Mac. There is no such thing as a no-brainer question here. The only no-brainer question is a question not asked.

     

    More information will lead to a solution .

    Are the two columns something like income and expenditure?

    Is the next column perhaps bank balance?

    I am just guessing here. Please explain .

    Regards,

    Ian.

  • by Wayne Contello,

    Wayne Contello Wayne Contello Sep 15, 2016 5:30 AM in response to kpcbloom
    Level 6 (19,282 points)
    iWork
    Sep 15, 2016 5:30 AM in response to kpcbloom

    maybe this is what you want?

     

    Screen Shot 2016-09-15 at 7.28.52 AM.png

     

    D2=IF(COUNTA(B2:C2)>0, SUM($C$2:C2)−SUM($B$2:B2), "")

     

    this is shorthand for... select cell D2, then type (or copy and paste from here) the formula:

    =IF(COUNTA(B2:C2)>0, SUM($C$2:C2)−SUM($B$2:B2), "")

     

    E2=IF(D2="", "", MAX($E$1:E1)+1)

    F2=IF(AND(COUNTA($B$2:C2)=COUNTA(B:C), COUNTA(B2:C2)>0, COUNTA(B3:C3)=0), D2, "")

     

    select cells D2 thru F2, copy

    select cells D2 they the end of column F, paste

  • by kpcbloom,

    kpcbloom kpcbloom Sep 15, 2016 3:18 PM in response to Wayne Contello
    Level 1 (12 points)
    iWork
    Sep 15, 2016 3:18 PM in response to Wayne Contello

    Ian and Wayne

    Yes, this is similar to the sheet with which I am working.  As the cumulative total moves down column D, I want that result to be reflected and appear in F2.  I'm going to experiment with some of these suggestions and try the formula listed for F2.

    Currently I'm using a formula in column D that basically says -B3+C3+D2.  I then dragged that formula down Column D so it completes the calculation from B and C and adds the result to the previous cell in column D to build a running total.

    Thanks for the quick response.  I've been pretty frustrated as I move from an excel sheet to numbers.  All the formulas (which my son helped me create) did not transfer so I'm rebuilding a bit.

     

    Thanks again.