Tuesday, December 6, 2011

yogi_Compute Running Balances For Transactions In Date Descending Order

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user brder said:
Create an ArrayFormula for a Date Descending Running Balance
I have searched this extensively and have not found an answer. I have a simple sheet for tracking bank transactions. The columns (left to right) are
Date | Transaction | Amount | Balance.Like any banking website, the transactions are date descending (e.g. newest at the top). So to keep the balance requires some trickery. I have the following formula which appears to work.
SUM(OFFSET($F4, 0, -1, ROWS(E1:E9)-(ROW()-ROW(F4)-1)
This seems to work okay. The problem is when I insert a new row, the balance cell is blank and I have to drag my formula down.
ArrayFormula seems to be the way to go but I cannot get a version that works properly.
Alternatively, I'd settle for being able to add a custom addRow menu item that runs a script to do this but I'm not sure how to do that either.
ollowing is solution to the problem