Referencing the cell above

[Paul_Danyliuk]

I personally think that there should NOT be a generic previousRow variable because as you said there is lot of ambiguity of what it should refer to. E.g.

• If it’s a row whose RowID is the preceding one (i.e. the last undeleted row before this row)
  — it will be confusing for those who filter and sort and group.
  — not friendly to Coda newcomers (those who’d need this variable in the first place, since the more advanced users can just write a formula they need)

• If it’s a visually previous row in current table or view
  — it will change every time you filter or sort or group
  — and besides, it would then have to calculate differently for different views, which isn’t easy to implement.

• Make it a function with parameters like a series of filters and sorts
  — at this point it becomes no easier than adding that column and writing that formula (and actually seeing which rows it calculates)

So yeah, I think that it’s better to keep it an explicit thing. Besides, the formulas for finding the previous row are not that hard.

for RowID-based order (need to add RowID column as thisRow.RowID()):

thisTable.Filter(CurrentValue.RowID < thisRow.RowID).Sort(true, RowID).Last()

for visual position-based order (based on drag-n-drop row order in the master table, pre-sorting and pre-filtering):

thisTable.Nth(thisTable.Find(thisRow) - 1)

for visual position-based order in a filtered view (based on drag-n-drop row order in the master table, still pre-sorting):

MyView.Nth(MyView.Find(thisRow) - 1)

Same as above, but you need to account for ordering on ColumnA first, ColumnB second:

MyView.Nth(MyView.Sort(true, ColumnB).Sort(true, ColumnA).Find(thisRow) - 1)

for arbitrary value order where you know that values are sequential, unique, and increase at constant rate (e.g. one row per day with no days skipped):

thisTable.Filter(CurrentValue.Date = thisRow.Date - 1).First()

etc.

Note that in either case the previous row calculation will be a “heavy formula” and become exponentially slower as your table becomes larger. So if you can employ some other strategy than a live calculation of a running total / reference to the previous row etc, consider some other strategy:

Realistic number of rows in a table

Short answers; will expand sometime later in a blog post Expensive formulas: Operations that become exponentially slower as the data set grows. Example: in Classes table, a column that looks up all previous classes by the same student. Every time a class is added, it recalculates all rows, and every time it’s comparing each row with every other row of the data set that grows (hence N^2 operations) Along with the previous one: operations that unnecessarily recalculate data that is known to…

---

Also my original post is somewhat invalid after all this time. @shishir’s sample doc shows how it’s best done (with a separate column).