# How do I create a predictive balance formula?

Options
edited 07/19/22

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?

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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)

• Options

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.

• ✭✭✭✭✭✭
Options

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?

• Options

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.

• ✭✭✭✭✭✭
Options

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)

• Options

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

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

• ✭✭✭✭✭✭
Options

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

• edited 07/22/22
Options

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.

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!