Sheets with cumulative values
We want a low-maintenance sheet that
- Uses column formulas
- Accepts new rows via forms
- Contains a running total in each row (e.g. budget minus spend)
This would allow each row to show the diminishing budget — a burndown report.
We found this answer by Leibel S. that is letting us create column formulas that reference the row above: https://community.smartsheet.com/discussion/73509/row-referencing-cell-above
This works and seems like this should be a great help – the “Last Month” columns use Leibel’s idea. They’re a row-shifted copy of the columns to their right. Row 2, for instance, works appropriately except for “Budget Remaining” – ignore that for now. (We did have to add a Row 1 and use column formulas with IF statements that treat row 1 differently, resulting in some #INVALID COLUMN VALUE errors, but they don’t impact performance.)
In this example, Budget Remaining and Budget Minus Spend Last Month are redundant columns — they should contain the same values — but Budget Minus Spend needs a running total to subtract from. It’s using the Budget as of Last Month. But then, when I try to set Budget Remaining equal to Budget Minus Spend, we get circular reference errors.
In theory, I understand that the reference is circular from column-to-column, but it’s not circular within the same row. Unfortunately, Leibel’s trick isn’t protecting us from this error.
I feel like burndown tables with column formulas must be something that SmartSheet is able to do. Any tips?
Here are my formulas:
Budget as of Last Month: =INDEX([Budget Remaining]:[Budget Remaining], [Row Number]@row - 1)
Budget Remaining (erroring out): =IF([Row Number]@row = 1, [SO Total]@row, [Budget Minus Spend]@row)
Budget Minus Spend Last Month: =INDEX([Budget Minus Spend]:[Budget Minus Spend], [Row Number]@row - 1)
Budget Minus Spend: =IF([Row Number]@row = 1, [SO Total]@row, [Budget as of Last Month]@row - [Total Spend This Month]@row)
Spend as of Last Month: =INDEX([Total Spend This Month]:[Total Spend This Month], [Row Number]@row - 1)
Total Spend This Month: =IF([Row Number]@row = 1, 0, [Total - Actuals]@row)
