How do I create a predictive balance formula?

Clover10123 ✭✭
edited 07/19/22 in Formulas and Functions

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


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!