If you add a formula that requires you have to manually fill it down a column, you might run into some problems. When Tiller adds new Transactions and needs to add new rows to fit them, the formula wouldn’t be in those new rows. That’s why using an arrayformula in the top row is a better approach.
I suppose you could add a lot of empty rows and fill the formula into those empty rows, so Tiller wouldn’t create new rows without the filled down formula. But if you ever forgot to keep enough empty rows filled, you would have a problem.
Also, you are likely taking a performance hit with all those formulas.
Regarding making the formula cumulative up, you could probably write a formula in the bottom row (actually 2nd to bottom row so, bottom row minus current row) and fill the formula UP the sheet. Again, I wouldn’t recommend it.
You might look at this arrayformula which when put in a new column can create a running total of another column. If the column you want to total is Column B, then this would go in the running total column top row:
Finally, I would highly recommend you do these running balances in a separate sheet. I know you must have some reasons for using the Transactions sheet, but I don’t think using the Transactions sheet for the calculations you want is a best practice. Consider the Transactions sheet as a database. And if you want a certain view of that database, with running totals, create a new sheet with the view you want. You won’t be able to edit the database from the view sheet, but it is much more efficient and powerful doing it that way.
I know this is an old thread but I’m new here and trying to add this functionality to my sheet in excel as I do want to see a running balance on my transactions. I was able to add something that shows each account balance but it’s the current balance as of today and not on the date of the transaction. I feel like the formula you add may have done that but I don’t know how to convert it to excel. Thanks!
Hi @tdlaryea3 ,
Welcome to the Tiller community. As this thread is 2 years old, i can’t remember all the details.
But i’m pretty sure the solution discussed involved the running balance based on the prior transactions up until and including the row that running balance is listed on. The date of the transaction didn’t have anything to do with it, just whether the transaction was above or on the same row.
I’m not sure that answers your question, but hopefully it does.
Correct, I mentioned date but i was referring to the row and ideally what I’m looking for is the formula you made above that is for Google Sheets to be provided in Microsoft Excel. Is that something you can provide?
And this is a screenshot of how it looks. I want to see the running balance of all my accounts from the transactions sheet and this lets me do it. So just sharing in case anyone else is interested in this.
You have to sort your transactions from oldest to newest and I added an opening balance at the top with an old date so it always starts with that balance. As you go through and add transactions, it matches the account name to keep track of the running balance.
Hi all,
Also looked into this and created an array formula that can be entered into the header row on the transactions sheet. No sorting required, no pull-down of formulas after new transactions are filled.
Could you please edit the post to put the code in a Preformatted text block?
That way it’s easy to copy and it won’t contain unwanted curly quotes, etc.
Also, I’m curious how many transactions do you have and do you notice any performance slow-down after adding the formula?
Is your Amount column really column M? That seems so far to the right in the Transactions sheet for the amount
Thanks! Took a bit to figure it out, and I’m getting better with lambda, which is proving useful. (I had looked for the code block–it was hidden under the gear button!)
I have ~7000 rows. Just tested a cat change w/ the formula and w/o and I didn’t see a difference.
This is fantastic @kyle.sullivan.me ! Similar to @Mark.S 's question can you please clarify which column acctsAll needs to be? or is it interchangeable between “Account” and “Account #”?
Sorry for the confusion. acctsAll could either be account # or account name (just needs to be a unique account identifier). I definied it with account names.
To add to this—this solution is obviously not connected to the account balances recorded elsewhere in tiller, so if transactions were deleted, not recorded, duplicated, etc, then the balance shown using this formula may not match your actually account balance.
PRO TIP: I’ve been finding that using the running balance is another great way to reconcile accounts and find any errors or missing transactions. I’ve added conditional formatting to compare the latest ‘running balance’ value for each account to the ‘balance history’ worksheet.
EDIT #1: on my formula above to include rounding (as I was getting some errors when creating a system to check calculated balances against tiller-pulled balances).
EDIT #2: Modification using indirect so that when manual transactions are added, the relative reference doesn’t change to row 3.
I didn’t verify this idea, but did you try removing the arrayformula function and just keeping the contents of it? each of the first 3 rows need to be mapped to the column letters your date, amount, and account columns are actually.
For example, my amounts are in column M. If your amounts are column N, change the M’s above to N’s.
The 4th line should be this (no arrayformula function wrapping it): ROUND(MAP(acctsAll,datesAll,LAMBDA(acctRow,dateRow,SUMIFS(amtsAll,acctsAll,acctRow,datesAll,“<=”&dateRow))),2))
