Sometimes you need to implement iterative calculation where a value in the next row depends on the value in the previous row (e.g., interest calculator).
To do this in Excel / Sheets, you simply reference the cell above by specifying the row as current row index minus 1. In Coda, though, there is no concept of “cell reference” or a ready-to-use row index, and if you try to enter the formula for
Value column as
=Value.Nth(…), you will get a circular reference error.
Solution? — Construct something to serve as a row index and use a lookup!
- Create a column
Row IDwith formula:
- Create a column
Row indexwith formula:
Rank(thisRow.[Row ID], thisTable.[Row ID], true)
This is to maintain a continuous count in case you delete rows, 1-based.
- For your
Valuecolumn, write a recursive formula with a lookup to the previous row:
thisTable.Lookup([Row index], thisRow.[Row index] - 1).Value * <rest of formula>
- If you’re referencing the same column, you probably need a special condition for the very first row. So wrap it with
If(thisRow.[Row index] = 1, [Initial value], thisTable.Lookup([Row index], thisRow.[Row index] - 1).Value * <rest of formula>)
- If you need to insert rows in the middle and thus cannot depend on larger Row IDs to correspond to further iteration steps, you can lookup by something else, e.g. manually entered years column.
- If that column does not contain sequential numbers (e.g., years are 2019, 2020, 2030, 2100…), you can still infer row index with this formula:
thisTable.Filter(Year < thisRow.Year).Count()
(will be zero-based)
- To make calculations simpler, it makes sense to introduce a separate column(s) whose value(s) is/are calculated via lookup from the previous row.
Real use case: https://coda.io/d/Paul-s-Plan-Demo_dqOyKWdqtC6/_su3EB
(see Progress table:
Available stash is previous row’s
End of day stash, and
Penalty is previous row’s
Remaining multiplied by