How to Get a Balance Column to Show the Balance Remaining in Each Row
I am trying to create a budget sheet which shows the remaining balance for each line. Here are my columns:
- Order: This column has the following formula =MATCH([Row ID]@row, [Row ID]:[Row ID], 0) to allow me to move around items to better fit the budget balance for each period.
- Row ID: This is the auto-numbered smartsheet number for each row (0001 is the 1st one)
- Description: This is a text field where I input the description of the expense or revenue.
- Date: This is a date column where I input the date of the expense, or revenue
- Amount: This is where I input the dollar amount of the expense (written as -$1.00) or revenue (written as $1.00)
- Balance: This is where I'm trying to have a running total of the current balance based on the "Amount" column, but I can't figure out the formula. I also want it to be a column formula so if I move a row to fix the budget, it will still calculate correctly.
Example: Basic Budget Flow
Order | Row ID | Description | Date | Amount | Balance |
---|---|---|---|---|---|
1 | 0001 | Revenue 1 | 10/1/2024 | $100.00 | $100.00 |
2 | 0002 | Expense 1 | 10/2/2024 | -$25.00 | $75.00 |
3 | 0003 | Revenue 2 | 10/3/2024 | $100.00 | $175.00 |
4 | 0004 | Expense 2 | 10/4/2024 | -$25.00 | $150.00 |
5 | 0005 | Expense 3 | 10/5/2024 | -$25.00 | $125.00 |
There will be times when i will need to move expenses to avoid negative balances, and I would want the Balance column to accommodate that movement while still giving valid values. There are also times when I'll need to add additional expenses in between rows.
Example: Moved rows with proper Balance calculation.
Order | Row ID | Description | Date | Amount | Balance |
---|---|---|---|---|---|
1 | 0001 | Revenue 1 | 10/1/2024 | $100.00 | $100.00 |
2 | 0002 | Expense 1 | 10/2/2024 | -$25.00 | $75.00 |
3 | 0004 | Expense 2 | 10/3/2024 | -$25.00 | $50.00 |
4 | 0003 | Revenue 2 | 10/4/2024 | $100.00 | $150.00 |
5 | 0005 | Expense 3 | 10/5/2024 | -$25.00 | $125.00 |
Example: Adding new Row
Order | Row ID | Description | Date | Amount | Balance |
---|---|---|---|---|---|
1 | 0001 | Revenue 1 | 10/1/2024 | $100.00 | $100.00 |
2 | 0002 | Expense 1 | 10/2/2024 | -$25.00 | $75.00 |
3 | 0006 | Expense 4 | 10/2/2024 | -$25.00 | $50.00 |
4 | 0003 | Revenue 2 | 10/3/2024 | $100.00 | $150.00 |
5 | 0004 | Expense 2 | 10/4/2024 | -$25.00 | $125.00 |
6 | 0005 | Expense 3 | 10/5/2024 | -$25.00 | $100.00 |
Is there a formula I can create for that balance column based on my requirements above?
Thanks in advance!
Answers
-
Hi @CathyP712
How about summing on the rows that have an order number equal to or less than the current row:
=SUMIF(Order:Order, <=Order@row, Amount:Amount)
That will give you a balance like this:
Then if you move rows, as the order is updated, it still works:
And if you add a row, again the order updates, and it still works:
Hope this helps.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!