kpcbloom

Q: Identifying a moving cell

Thanks for the earlier reply but here is my question once again.  In the following sheet, I want to move the calculation in column E to F2.  The calculation of column E changes each time an entry is made.  The running total column (E) includes a pretty simple calculation  -C2+D2+E1.

 

 

 

 

 

 

 

 

Date

Category

Debit

Credit

 

 

Running Total

 

 

 

 

 

 

 

6/12/2013

Checking

 

$10,000.00

$10,000.00

 

 

 

savings

 

$10,000.00

$20,000.00

 

 

 

Cash

 

$400.00

$20,400.00

 

 

 

Medical

$ 100.00

$0.00

$20,300.00

 

 

 

Food

$ 900.00

 

$19,400.00

 

 

 

Spending

$ 600.00

 

$18,800.00

 

 

 

Gas

$ 100.00

 

$18,700.00

 

 

 

Utilities

$ 100.00

 

$18,600.00

 

 

 

Phone

 

$200.00

$18,800.00

 

 

 

TV

$ 10.00

 

$18,790.00

 

 

 

Gifts

$ 655.00

 

$18,135.00

 

 

 

Misc

$ 1,000.00

 

$17,135.00

 

 

 

Vacation

$ 5,000.00

 

$12,135.00

 

 

 

 

 

 

$12,135.00

 

 

 

 

 

 

 

 

 

MacBook Air, Mac OS X (10.6.8)

Posted on Sep 19, 2016 2:43 PM

Close

Q: Identifying a moving cell

  • All replies
  • Helpful answers

  • by Wayne Contello,

    Wayne Contello Wayne Contello Sep 19, 2016 3:12 PM in response to kpcbloom
    Level 6 (19,374 points)
    iWork
    Sep 19, 2016 3:12 PM in response to kpcbloom

    Like your previous post, the description of what you want is unclear.  What does it mean

    " I want to move the calculation in column E to F2"?

     

    you cannot move the whole range to cell F2, so, do you want the last entry in column E moved to F2?  where "last" means the entry in column E with the highest row value?

     

    highlight the cell you think should go in F2 and explain why that is

  • by t quinn,

    t quinn t quinn Sep 19, 2016 8:08 PM in response to kpcbloom
    Level 5 (5,063 points)
    Mac OS X
    Sep 19, 2016 8:08 PM in response to kpcbloom

    Hi kcpbloom,

     

    Your question is unclear to me but perhaps this is waht you are looking for.

     

    If you double click on the column you can get it to lift off the table. You can then drag it over to where you want it.

     

    quinn

  • by Barry,

    Barry Barry Sep 20, 2016 1:29 AM in response to kpcbloom
    Level 7 (32,633 points)
    iWork
    Sep 20, 2016 1:29 AM in response to kpcbloom

    "The running total column (E) includes a pretty simple calculation  -C2+D2+E1."

     

    In your sample table, C2 contains the text 'Debit', D2 contains the text 'Credit', and E1 appears to be empty.

     

    Column F is the narrow column.

     

    The column labeled "Running total" is column G.

     

    If I am correct in devining what you want, the formula below should do the job.

     

    Enter your starting balance (0.00) in G3, immediately below "Running Total."

    Enter this formula in G4:

    =G3-C4+D4

     

    Fill the formula down to the end of column G.

     

    To prevent repeated occurrences of the current running total in empty rows, use this revised version of the formula, which prevents calculation until there is a value entered in 'this row' of column C or column D:

    =IF((LEN(C)+LEN(D))<1,"",G3-C4+D4

     

    Regards,

    Barry

  • by kpcbloom,

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

    Thanks Wayne.

    Sorry for not being more clear.  Yes, I want the last number in column E, the one with the highest row value,  to be displayed in a cell in column F.  The total and cell location in column E will change as additional entries are made.  So, I want to have the last number in column E to be displayed in Column F.

     

    Thanks again for your assistance.  I was not sure how to highlight so I hope this explanation makes sense.

  • by kpcbloom,

    kpcbloom kpcbloom Sep 21, 2016 3:41 PM in response to Barry
    Level 1 (12 points)
    iWork
    Sep 21, 2016 3:41 PM in response to Barry

    Thanks Barry,

    I'm not communicating clearly so I'm trying again.  I want to take the calculated running total (Column E) and have the last cell in that column be transferred to the cell I have labeled Running Total.  The last cell will move down Column E as I add more credits and debits so I said it was "moving".  Not sure that was the correct terminology.  (I put the text in the cell in row G to try to illustrate.  I can now see that it is column G and not F.)

    Thanks for all the assistance.  With the communities assistance, I'm sure I can get this problem solved. 

  • by Wayne Contello,

    Wayne Contello Wayne Contello Sep 21, 2016 7:01 PM in response to kpcbloom
    Level 6 (19,374 points)
    iWork
    Sep 21, 2016 7:01 PM in response to kpcbloom

    why was the solution I provided in your previous post not what you are looking for?

     

    because you want the last cell val to be presented in a fixed location?

  • by Wayne Contello,

    Wayne Contello Wayne Contello Sep 21, 2016 7:25 PM in response to kpcbloom
    Level 6 (19,374 points)
    iWork
    Sep 21, 2016 7:25 PM in response to kpcbloom

    maybe this will work?

     

    Screen Shot 2016-09-21 at 9.24.26 PM.png

     

    in column F enter the formula like this...

     

    F2=IFERROR(IF(AND(E2>0, E3=""), E2, ""), "")

     

    select cell F2, copy

    select cells F2 thru the end of column F, paste

     

    G1="Running Total: "&MAX(F)

  • by Barry,Solvedanswer

    Barry Barry Sep 22, 2016 7:13 AM in response to kpcbloom
    Level 7 (32,633 points)
    iWork
    Sep 22, 2016 7:13 AM in response to kpcbloom

    Hi k'

     

    A "running total" generally refers to the figures you have in column E—each value shows the total to that point in time.

     

    You want the 'current total' or 'most recent total' to be displayed in a specific cell in column G.

     

    The "current total," simply expressed is the 'opening balance' plus 'the sum of the credits' minus 'the sum of the debits'.

     

    In your table, the 'opening balance' is zero, and may be ignored.

    The credits are recorded in column D, so the 'sum of the credits' is SUM(D).

    The debits are recorded in column C, so the 'sum of the debits' is SUM(C).

     

    The formula for the current balance (or current total) is =SUM(D)-SUM(C)

    This formula may be placed in any cell in any column other than D or C.

    Screen Shot 2016-09-21 at 9.40.15 PM.png

    Regards,

    Barry

  • by kpcbloom,

    kpcbloom kpcbloom Sep 22, 2016 7:15 AM in response to Barry
    Level 1 (12 points)
    iWork
    Sep 22, 2016 7:15 AM in response to Barry

    Thanks Barry.

    This formula seems to have solved my issue and is working in my sample file.  I will be testing it in more detail.

    Thanks so much for your patience.

    k

  • by kpcbloom,

    kpcbloom kpcbloom Sep 22, 2016 7:17 AM in response to Wayne Contello
    Level 1 (12 points)
    iWork
    Sep 22, 2016 7:17 AM in response to Wayne Contello

    Thanks for all your help Wayne.  Your work led me to a better explanation of what I needed and I think Barry (posting as well) provided a simple solution that will work for my application.

    Kpac