Sheets with cumulative values


Sheets with cumulative values

We want a low-maintenance sheet that

  1. Uses column formulas
  2. Accepts new rows via forms
  3. 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:

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


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!