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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.9K Get Help
- 474 Global Discussions
- 208 Use Cases
- 517 Announcements
- 5.6K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 84 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!