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 ID`

with formula:`thisRow.RowId()`

- Create a column
`Row index`

with 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
`Value`

column, 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`

:

`If(thisRow.[Row index] = 1, [Initial value], thisTable.Lookup([Row index], thisRow.[Row index] - 1).Value * <rest of formula>)`

Special cases:

- 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.

Demo:

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 `Penalty rate`

)