How do I create a predictive balance formula?

Clover10123 · 07/19/22

Screen Shot 2022-07-19 at 12.34.14 PM.png

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?

Paul Newcome · 07/20/22

If it is set as an auto-number column, it will populate as soon as the sheet is saved.

Paul Newcome · 07/19/22

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)

Clover10123 · 07/19/22

https://community.smartsheet.com/discussion/comment/336709#Comment_336709

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.

Paul Newcome · 07/20/22

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?

Clover10123 · 07/20/22

Screen Shot 2022-07-20 at 1.55.50 PM.png

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.

Paul Newcome · 07/20/22

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)

Clover10123 · 07/20/22

Screen Shot 2022-07-20 at 2.08.40 PM.png

Hmm, I may be a little bit confused? Did I do this correctly?

Screen Shot 2022-07-20 at 2.09.32 PM.png

Was something supposed to go into the Auto column as well?

Paul Newcome · 07/20/22

If it is set as an auto-number column, it will populate as soon as the sheet is saved.

Clover10123 · 07/22/22

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.

Screen Shot 2022-07-22 at 2.02.07 PM.png

Paul Newcome · 07/25/22

Happy to help. 👍️

How do I create a predictive balance formula?

Best Answer

Answers

Help Article Resources

Categories