Sheets with cumulative values

Colby_Nielson

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: 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)

Paul Newcome

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)

Paul Newcome

What data point are they actually putting in via form?

Colby_Nielson

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.

Paul Newcome

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)

Colby_Nielson

Thanks Paul, looks like we were able to get it working!

Paul Newcome

Happy to help. 👍️