Sheets with cumulative values
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)
Best Answer
-
To get a cumulative value on each row, you would leverage the [Autonumber] column.
=SUMIFS([Spend This Month]:[Spend This Month], Autonumber:Autonumber, @cell<= Autonumber@row)
Answers
-
What data point are they actually putting in via form?
-
The goal is for them (PMs) to only put in a dollar amount for "Spend This Month". The sheet would drop that data at the bottom, and the calculations would automatically populate the new SO total and so on.
-
To get a cumulative value on each row, you would leverage the [Autonumber] column.
=SUMIFS([Spend This Month]:[Spend This Month], Autonumber:Autonumber, @cell<= Autonumber@row)
-
Thanks Paul, looks like we were able to get it working!
-
Help Article Resources
Categories
Check out the Formula Handbook template!