Referencing the cell above

#1

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!

  1. Create a column Row ID with formula: thisRow.RowId()
  2. 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.
  3. 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>
  4. 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:

  1. 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.
  2. 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)
  3. 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)

5 Likes
Transpose table
Growth calculation
#2

Nice! I wrote something related here:

6 Likes
#3

I knew this should’ve been solved already, but did not find the relevant topic in the community.