+ Reply to Thread
Results 1 to 16 of 16

Running Balance Formula Column P of Register

  1. #1
    Forum Contributor
    Join Date
    02-26-2023
    Location
    Lousianna
    MS-Off Ver
    Microsoft 365
    Posts
    1,271

    Running Balance Formula Column P of Register

    Hello,

    Can formulas keep a running balance in column P of the Register sheet?

    Thank you
    Attached Files Attached Files

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,326

    Re: Running Balance Formula Column P of Register

    Are you referring to a running sum beginning in P18? If so, try =SUM($O$18:O18)
    HTH
    Regards, Jeff

  3. #3
    Forum Contributor
    Join Date
    02-26-2023
    Location
    Lousianna
    MS-Off Ver
    Microsoft 365
    Posts
    1,271

    Re: Running Balance Formula Column P of Register

    Thanks I want each row to update amounts beginning with P11. I realize it isn't continuous is why I am having trouble.

    I want it this way so I can sort the table but keep the accounts at top.

  4. #4
    Forum Contributor
    Join Date
    02-26-2023
    Location
    Lousianna
    MS-Off Ver
    Microsoft 365
    Posts
    1,271

    Re: Running Balance Formula Column P of Register

    I filled in column P manually with amounts that I would like formulas to achieve.

    Is this possible?

    I don't think the amounts are correct carried down
    Attached Files Attached Files
    Last edited by billy60; 01-12-2024 at 05:52 PM.

  5. #5
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,326

    Re: Running Balance Formula Column P of Register

    Not possible that I can see. That blank row throws a wrench into everything. I'll give this to the pros.

  6. #6
    Forum Contributor
    Join Date
    02-26-2023
    Location
    Lousianna
    MS-Off Ver
    Microsoft 365
    Posts
    1,271

    Re: Running Balance Formula Column P of Register

    I tried another way. I removed that column from table and have the running totals in column Q. The only problem I see is the formulas for the running totals need to be far enough down to cover future entries.
    Attached Files Attached Files

  7. #7
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,326

    Re: Running Balance Formula Column P of Register

    Make it part of the table and it will adjust along with the table.

  8. #8
    Forum Contributor
    Join Date
    02-26-2023
    Location
    Lousianna
    MS-Off Ver
    Microsoft 365
    Posts
    1,271

    Re: Running Balance Formula Column P of Register

    I run into the first row issue again though.

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    18,405

    Re: Running Balance Formula Column P of Register

    Perhaps this will help.
    In the file attached to post #4 I converted the data in rows 10:16 into an Excel table (Register_S).
    The formula for column P in that table is populated using: =SUM(INDEX(Resgister_S[[#All],[Running Balance]],ROW()-10),[@Deposit],-[@Payment])
    Column P in the Register_T table is populated using: =SUM(INDEX(Resgister_S[Running Balance],ROWS(Resgister_S[Running Balance])),SUM(INDEX([Deposit],1):[@Deposit],-SUM(INDEX([Payment],1):[@Payment])))
    Note that the original amounts were pasted into column R and column S shows the check. A few of the checks, such as in row 70, show as false however the amounts in columns P and R match to 2 decimal places (P70 displays 8,945.66 and R70 displays 8945.66 but contains 8945.66000000001).
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  10. #10
    Forum Contributor
    Join Date
    02-26-2023
    Location
    Lousianna
    MS-Off Ver
    Microsoft 365
    Posts
    1,271

    Re: Running Balance Formula Column P of Register

    Great! Thank you very much JeteMc.

    I get a warning: parts of your document may contain personal information and can't be removed by document inspector.
    Last edited by billy60; 01-12-2024 at 09:58 PM.

  11. #11
    Forum Contributor
    Join Date
    02-26-2023
    Location
    Lousianna
    MS-Off Ver
    Microsoft 365
    Posts
    1,271

    Re: Running Balance Formula Column P of Register

    I can't save document now without this warning popping up.

  12. #12
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    18,405

    Re: Running Balance Formula Column P of Register

    I don't get that warning, although I have seen it with other .xlsm files.
    When I looked again at the formula for cells P18 and down I found a mistake.
    The formula should read:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  13. #13
    Forum Contributor
    Join Date
    02-26-2023
    Location
    Lousianna
    MS-Off Ver
    Microsoft 365
    Posts
    1,271

    Re: Running Balance Formula Column P of Register

    That corrected the formula issue but I still cannot save the file now. I don't know what to do.

  14. #14
    Forum Contributor
    Join Date
    02-26-2023
    Location
    Lousianna
    MS-Off Ver
    Microsoft 365
    Posts
    1,271

    Re: Running Balance Formula Column P of Register

    I unchecked remove personal information under privacy and now I can save the file.

  15. #15
    Forum Contributor
    Join Date
    02-26-2023
    Location
    Lousianna
    MS-Off Ver
    Microsoft 365
    Posts
    1,271

    Re: Running Balance Formula Column P of Register

    Thank you very much for your help JeteMc

  16. #16
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    18,405

    Re: Running Balance Formula Column P of Register

    You're Welcome. Thank You for the feedback, for the added reputation and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Running balance column formula not auto adjusting properly when rows are inserted.
    By JPollard in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-08-2018, 11:58 AM
  2. Register balance
    By simplyimperfect86 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-02-2015, 06:28 PM
  3. Need formula for a check register (balance column)
    By dlperdue in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-23-2010, 01:47 PM
  4. Pivot table running balance column
    By Jon Sinclair in forum Excel General
    Replies: 0
    Last Post: 08-09-2007, 11:48 AM
  5. [SOLVED] How do I get the checkbook register to keep a running balance?
    By gmaw in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-10-2006, 11:40 AM
  6. Replies: 5
    Last Post: 08-04-2005, 04:05 PM
  7. [SOLVED] how : check register running balance is a stationary cell
    By Michele in forum Excel General
    Replies: 9
    Last Post: 01-19-2005, 02:06 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1