|
There are isolated problems with current patches, but they are well-known and documented on this site. |
Beginning balance in D4 – subsequent transactions in C5:C2000 – formula in D5:D2000 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(D6:D22),0,ROW(D6:D22))))
Is there an easier way?
Thanks
Linda
Linda
It’s nice to have a balance that never goes into overdraft 
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
Everything I see in your example suggests that the offset method will work – as in the attachment
Linda
Note everything that Steve says – the cautions are important. The SUM method he suggests works too for the example you gave and is likely to be resillient unless you do anything really tricky with adds and subtracts directly into the balances.
=OFFSET(H2,COUNT(F:F),-2)
Relies on the fact that COUNT returns a result that is a count of “filled in” cells – all sorts of things are counted as filled in. My assumption therefore is that your column is “clean” – if it’s not avoid this method.
OFFSET is a nifty function that makes a relative reference from a defined cell e.g. OFFSET(A1,+5,+3) references the cell 5 rows down and 3 across – i.e. D6.
OFFSET(A1,0,0) references itself and negative numbers can be used too e.g. OFFSET(B2,-1,-1) is A1.
To work out what formulae do – simply look at them one by one and dive into help if necessary..
e.g. enter =COUNT(F:F) into a cell and see its result.
Then, in a separate cell, see what the OFFSET does if the explanation above isn’t clear enough.
On a totally different topic – OFFSET is really useful for making total lines really include all data! If you have a column of data A1:A99 and want to sum it, many people put =SUM(A1:A99) in cell A100.
This is great until they add a new data entry line in A100 and displace the total down to A101. Examination will show that the TOTAL has missed out adding the new data at A100. If the original sum had been coded as =SUM(A1:OFFSET(A100,-1,0)) there would be no such problem.
Linda
Note everything that Steve says – the cautions are important. The SUM method he suggests works too for the example you gave and is likely to be resillient unless you do anything really tricky with adds and subtracts directly into the balances.
=OFFSET(H2,COUNT(F:F),-2)
Relies on the fact that COUNT returns a result that is a count of “filled in” cells – all sorts of things are counted as filled in. My assumption therefore is that your column is “clean” – if it’s not avoid this method.
OFFSET is a nifty function that makes a relative reference from a defined cell e.g. OFFSET(A1,+5,+3) references the cell 5 rows down and 3 across – i.e. D6.
OFFSET(A1,0,0) references itself and negative numbers can be used too e.g. OFFSET(B2,-1,-1) is A1.
To work out what formulae do – simply look at them one by one and dive into help if necessary..
e.g. enter =COUNT(F:F) into a cell and see its result.
Then, in a separate cell, see what the OFFSET does if the explanation above isn’t clear enough.
On a totally different topic – OFFSET is really useful for making total lines really include all data! If you have a column of data A1:A99 and want to sum it, many people put =SUM(A1:A99) in cell A100.
This is great until they add a new data entry line in A100 and displace the total down to A101. Examination will show that the TOTAL has missed out adding the new data at A100. If the original sum had been coded as =SUM(A1:OFFSET(A100,-1,0)) there would be no such problem.
Everything I see in your example suggests that the offset method will work – as in the attachment
Linda
It’s nice to have a balance that never goes into overdraft
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
Donations from Plus members keep this site going. You can identify the people who support AskWoody by the Plus badge on their avatars.
AskWoody Plus members not only get access to all of the contents of this site -- including Susan Bradley's frequently updated Patch Watch listing -- they also receive weekly AskWoody Plus Newsletters (formerly Windows Secrets Newsletter) and AskWoody Plus Alerts, emails when there are important breaking developments.
Notifications
