# Thread: Running Total Formula (XP)

1. ## 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. ## 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. ## 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.

aunt linda

4. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## Re: Running Total Formula (XP)

Perfect - THANK YOU!

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

Aunt Linda

14. ## 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. ## 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 Last

#### Posting Permissions

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