Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Running Total Formula (XP)

    Beginning balance in D4 - subsequent transactions in C5:C2000 - formula in D52000 subtracting current transaction from previous balance. What I need is a formula in a non-print area cell that always shows the current balance - this is the one I currently use: =INDIRECT("D"&MAX(IF(ISBLANK(D622),0,ROW(D622))))

    Is there an easier way?

    Thanks

    Linda

  2. #2
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Total Formula (XP)

    Linda
    It's nice to have a balance that never goes into overdraft <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    One way is to make your "balance cell" calculate from transactions. = Opening Balance - SUM(TransactionColumn)
    e.g. assuming your transaction values are column C, and your opening balance was in Cell D1the formula =D1-SUM(C:C) does the trick

    Personally, if I have this kind of open 'list' I also tend to have a pivot table summarising it by periods - the totals are easy to come by from the pivot table <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Total Formula (XP)

    I obviously didn't explain myself very well - I show my beinning balance in cell G4. In a cell I don't wish to print I want to always see the CURRENT balance .

    In Cells G6 through infinity I will have a formula that displays the current balance - today that balance might be in G200 - tomorrow it might be in G2000.

    Hopefully I've been more clear - it hasn't been a very clear day for me.

    Thanks in advance

    aunt linda

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Running Total Formula (XP)

    As Andrew stated shouldn't the current balance be the starting balance minus the sum of all the transactions?
    <pre>=G6-Sum(C:C)</pre>


    As you add more transactions to col C you will add or subract more from G6 to give the current balance.

    Steve

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Total Formula (XP)

    It, of course, works perfectly. And you might be able to help me if I told you the WHOLE story about this particular worksheet - boy, what a day I'm having!

    The first 5 rows are filled with account identification stuff - for example, PO#, Invoice Maximum, Account #, Company Name, etc.

    The other part of the story I omitted is that the final current balance is the result of a formula that subtracts the amount spent from the current balance, then adds any encumbrances, then subtracts any disencumbrances, and then adds any credits - I'm sure that's what's making this so difficult - for me anyway.

  6. #6
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Total Formula (XP)

    Linda
    Editted to remove comments put in in parallel with Linda's previous post

    An alternative method could involve the use of dates.

    For instance, if each transaction has a date (assuming column(A) has ascending dates) then you could do a VLOOKUP for latest date and find the balance column.
    =VLOOKUP(TODAY(),A:G,7,TRUE)

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Total Formula (XP)

    It, of course, works perfectly. And you might be able to help me if I told you the WHOLE story about this particular worksheet - boy, what a day I'm having!

    The first 5 rows are filled with account identification stuff - for example, PO#, Invoice Maximum, Account #, Company Name, etc.

    The other part of the story I omitted is that the final current balance is the result of a formula that subtracts the amount spent from the current balance, then adds any encumbrances, then subtracts any disencumbrances, and then adds any credits - I'm sure that's what's making this so difficult - for me anyway.

  8. #8
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Total Formula (XP)

    Linda
    I'm confused - your second reply is an awful lot like your first reply so I'm not sure if you're rejecting the date method or not..

    I'll try an alternate method

    =OFFSET(G4,COUNT(G5:G2000),0)

    You may have to add or sutract a constant to the count to get precisely the right row.

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Running Total Formula (XP)

    I am also confused. If Andrew's latest post does not answer your question, could you post a little example file detailing what you want/need?

    Steve

  10. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Total Formula (XP)

    Here ya go - sorry I'm being such a pain - it's just really, really irritating that I can't make this work!

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Running Total Formula (XP)

    I am still not clear since you only have 1 formula and the setup does not look like your previous notes. Are you looking for the "total current balance":

    <pre>=F2+SUM(C:C,E:E)-SUM(B:B,D)</pre>


    If not could you provide a little explanation of what you want (referencing the example file).

    Steve

  12. #12
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Total Formula (XP)

    Everything I see in your example suggests that the offset method will work - as in the attachment <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  13. #13
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Total Formula (XP)

    Perfect - THANK YOU!

    Now please explain the formula to me so I can share with others.

    Aunt Linda

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Running Total Formula (XP)

    <pre>=OFFSET(H2,COUNT(F:F),-2)</pre>


    Gets the value that is 2 columns to the left (=-2), and the number of rows that equals the count of numbers in Col F down [Count(F:F)] from cell H2

    Steve

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Running Total Formula (XP)

    It is important to the results in Andrew's formula that there are no rows with contiaining non-numerics (text, errors, blanks) within the datarange. Thus different setups could result in incorrect results.

    The formula I gave in <post#=364795>post 364795</post#> will not have this problem.

    Another way to do it is to use the formula:
    <pre>=VLOOKUP(9.99999999999999E+307,F:F,1)</pre>


    Which will give the number that is furthest down in column F, even if there are blanks or text in the column. This Vlookup looksup the "large number" (max for excel) as it looks it will not find it so it keeps going further down the row, until it gets the last number, which it reports.

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

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