How do I create a predictive balance formula?
I'm not sure if this is even valid/possible, but I'm trying to create an accounting schedule which foresees upcoming payments and reflects the balance after the transaction. This image is a practice sheet with fictional values. In theory, I'd want the formula in Balance to always take the previous balance and add it to the amount, but there doesn't seem to be a way to make a column formula out of this.
The most important part is this: I want to write the formula in such a way that I can **reorder any row** and the balance will still be calculated correctly. For example, I want these rows to appear in the order that they actually occur. For example, say I've predicted row 6 to occur 7/16, but it actually takes place on 7/12 - I want to move that row up between rows 4 and 5 so that it is sorted by date, and then I want the balance calculation to accurately reflect the change over time.
What would be the best way to achieve the desired output?
Should I create a separate sheet and do the balance addition on the separate sheet?
Best Answer
-
If it is set as an auto-number column, it will populate as soon as the sheet is saved.
Answers
-
So it looks like maybe you want a running total of the Amount column based on the Due Date?
=SUMIFS(Amount:Amount, [Due Date]:[Due Date], @cell <= [Due Date]@row)
-
That's an interesting idea, but I don't think it's quite what I'm going for. I don't necessarily need each cell to contain an amount for each due date, I want the balance after each transaction.
Think of a bank transaction history, where it shows the total of your transaction and then the remaining balance at the time of the transaction's completion.
-
Right. The formula would go in the Balance column. It is based on the date because you said you wanted it to accommodate being sorted by date. Have you plugged it in to try it?
-
Here is the result of your formula in the balance column. I sorted the rows by due date, too. Notice line 4 that paying Github $4 on 7/11 results in me having $2000 more dollars than I did in the previous cell.
-
That is because it is adding up by date. I didn't realize you would have multiple entries on a single date.
Insert an auto-number column (called "Auto" in this example). Formatting doesn't matter.
From there insert a text/number column (called "Row" in this example) and use this column formula:
=MATCH(Auto@row, Auto:Auto, 0)
Then you can adjust the SUMIFS to say:
=SUMIFS(Amount:Amount, Row:Row, @cell <= Row@row)
-
Hmm, I may be a little bit confused? Did I do this correctly?
Was something supposed to go into the Auto column as well?
-
If it is set as an auto-number column, it will populate as soon as the sheet is saved.
-
Hey I think this actually worked! At first I thought the math was wrong (when I first posted this comment lol) but I realize now that I can re-order these rows by date and still get the right math.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!