Referencing the cell above

[Paul_Danyliuk]

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: Paul’s Plan — Demo
(see Progress table: Available stash is previous row’s End of day stash, and Penalty is previous row’s Remaining multiplied by Penalty rate)

[shishir]

Nice! I wrote something related here:

[Paul_Danyliuk]

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

[Filmos]

Just a heads-up, if you are adding multiple rows at once and want to reference the cell above, it is better to add your own ID system on creation of the rows instead of using RowID.

[Paul_Danyliuk]

A follow-up heads-up. It’s not better to add your own ID system as the above comment suggest, but to simply wait until all Row IDs are assigned:

Waiting for the rows to be saved to Coda (for Row IDs to be assigned)

When you create many rows at once in Coda (or lately, even one or two), their Row IDs are not assigned immediately but after some time. I assume that this happens because Coda needs to store the row on the backend first. It also applies some rate limiting, that’s why if you create lots of rows, their Row IDs will only appear one after one with some delay. You can observe this behavior by clicking the Create 20 rows button and looking at the Data table. Sometimes though you need to perform some …

[Eric_Hewitt]

Hi Shishir -

I have run into this twice now. Each time I do, I have to remember… How did I do that last time? Oh yeah, I need to see Shishir’s example. I find it. I copy/paste the PreviousRow.CumulativeTotal + Price formula, and it doesn’t work. It says, “Cannot find PreviousRow in this row.” I try stuff, then come back here and read more. Oh yeah, I have to add those pesky extra columns. Finally, I get it working.

Any chance this ability could be built into Coda so I can type “PreviousRow” into any table and have it just work?

Eric

[Eric_Hewitt]

Oh yeah, then I remember that if you insert rows in the list it breaks this completely so you have to add more columns. Argh!

[Johg_Ananda]

Are you manually clicking all those colors on to your code block text or do you have some shortcut or voodoo?

[shishir]

Definitely on the list - we have built prototypes of this but a couple tricky issues to work out so it’s not in a currently staffed story :(. The main tricky issue is that you need a way to specify an ordering to the PreviousRow algorithm (i.e. it likely needs to be a function, not a reference).

Perhaps worth a quick poll - what syntax would you want for a generic PreviousRow implementation? For example, would you want something that takes a series of filters and sorts as parameters?

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

[Ander]

Here’s one possible way of approaching the problem.

RelativeRow()

Gets a row based on its position relative to thisRow.

Sample Usage

RelativeRow(-1)

RelativeRow(2,2,2,-1)

RelativeRow(
  client=“Acme Ltd.”,       
  Sort(true, month),
  1,
  -13
)

Syntax

RelativeRow(filter, sort, type of row ID, relative position)

1) filter - which rows?

• 1 or “raw source” = all rows in source Table (default)
• 2 or “currently visible” = Filter currently applied to Table or View (note: these can differ, and the formula will automatically track that)
• 3 or “filter formula” = custom Filter formula entered as param

2) sort - which order?

• 1 or “raw source” = source Table unsorted (default)
• 2 or “currently visible” = Sorting currently applied to Table or View (note: these can differ, and the formula will automatically track that)
• 3 or “sort formula” = custom Sort formula entered as param

3) type of row ID - how to identify rows?

• 1 or “raw source” = source Table RowIDs (default)
• 2 or “raw source visual position” = source Table rows as they are visually positioned
• 3 or “currently visible visual position” = Visual position of rows in Table or View (note: these can differ, and the formula will automatically track that)

4) relative position - which location relative to thisRow?

• 1 or “previous row” = most common use case (default)
• 2 or “relative row” = a positive/negative/zero integer referencing which row position to target relative to thisRow, with 0 referencing thisRow, -1 referencing the previous row, -2 referencing the row before that, and 4 referencing the 4th row after thisRow, etc.

Notes

1. Smart parameter defaults would make it easy for new users to replicate the most common spreadsheet use cases—arguably easier than in spreadsheets themselves.
2. If the values of Table Filters and Sorts were accessible by the RelativeRow() formula, it would enable automatically syncing the two. Being able to access these values would also be valuable in other places, for the same reason.
3. Naturally following (2) above, would be the ability to save multiple pre-sets of Table Filters and Sorts to a list (perfect for the side bar!), and then toggle through those pre-sets from either the source Table or any View derived from it. In many cases, this would eliminate altogether the need for extra Views!

[Eric_Hewitt]

Hi Paul -

I definitely did not understand the complexity of the problem when I wrote my first reply above…

The reason I vote to add this is software “curb appeal”. When someone is first using a new application, you have maybe 15 minutes to hook them or lose them. In general, Coda has very good curb appeal–PreviousRow is an example of something that is simple in Excel/Sheets but complex in Coda.

As you point out, the challenge that Coda has is the reordering of rows. Let’s handle the two cases individually:

User Ordered:

• In this case PreviousRow should clearly return the row preceeding the current row based on the user selected order.

Sorted by Column:

• In this case, PreviousRow could either:
  • Return the row preceeding based on the current sort. As you point out, this is going to change with a different sort column or direction. So, let’s discard this idea.
  • Take a single column parameter. It uses this column to determine the previous row. It must use the same rules that table sorting uses when sorted by that column.

There is one more challenge. How to handle a user changing a table from User Ordered to Sorted. Options:

• Only support the “sorted” version of PreviousRow (i.e., a column must always be supplied). I think this would meet many common use cases and it seems like the simplest approach. Also, it works whether the user sorts or not, since it is always tied to a column.
• Also support for the User Ordered case. This mode would be selected by making the column parameter optional. This mode could cause confusion to users if they sort the table. Options:
  • Clearly indicate in the function help that this is independent of table sorting.
  • To be more safe, make the formula in error if the table is sorted.
  • Disable the ability to sort the table if this is used in a table’s column formula.
    I suggest User Ordered is a future feature of the PreviousRow function.
• PreviousRow functionality changes when the table is sorted or not. I don’t know the use case for this–where you actually want the formula result to change as a function of user selected sorting. Is there one?

In summary, I vote for a simple function:

PreviousRow(column)
Gets the row preceding this row as defined by column.

Examples:
Balance = PreviousRow(Date).Balance + thisRow.Amount

Inputs:
column: The column to use to determine the previous row. The returned row is the row that precedes this row when the table is sorted by column ascending.

I’m sure I’m missing corner cases, but I hope something nearly as simple as this is possible once all corner cases are handled.

Eric

P.S. There is ambiguity when two cells in the column have the same value. I noticed that when I have a date column and two cells with the same date, sorting the table forward vs. reverse does not swap the order of these rows. I would consider that a bug–since sort descending should be defined as the reverse of sort ascending. One potential solution to this is to have the sort algorithm always fall back to Row Id as its secondary sort order. In any case, this should be solved independently of PreviousRow, and PreviousRow should just use the same algorithm.

[Paul_Danyliuk]

@Eric_Hewitt

PreviousRow(column)

That’s actually a great idea! This function would be targeted at newcomers anyway, and for more complex scenarios there are always explicit formulas.

@Eric_Hewitt

I would consider that a bug–since sort descending should be defined as the reverse of sort ascending.

I wouldn’t — “descending” isn’t the same as “reverse of ascending”; sometimes I’d need a descending sort where an ascending one doesn’t even make sense (can’t come with an example right now but I sure encountered those.) Keeping the order of items that are considered equal is is an expected behavior of a stable sort. It gives you predictability about what the algorithm would do with those items, and IMO it’s much better than having them shuffled or even reversed — if I absolutely need them reversed I’d do that myself (e.g. by introducing an Ordered index column that’d go as a sequence of non-repeating numbers 1 to N, then sort descending on those). I can see, however, how for non-technical people there would be an expectation that flipping the sort should flip the entire table.

[Ander]

It’s possible that once it becomes easy to manage money in Coda, then more users with money will manage it in Coda. Which would benefit all of us.

This would require first class treatment of periodic and time-based sequences, with a UX that non-programmers could quickly manipulate without writing blocks of formulas.

And that might require innovating something that bridges the UX between coordinates-based spreadsheets and list-based relational tables.

[Eric_Hewitt]

Paul -

I see your point. And, I checked the precedent set by Excel and Sheets and they agree with you–sorting descending does not produce the reverse of sorting ascending if there are duplicates in the list. I concede and agree to not call it a bug. We both agree what is critical is consistency. Shuffling would thus be undesirable. I do feel that my proposed behavior would be both consistent and eliminate some corner cases for end users.

For example:

1. Create table with Date, Amount, and running Balance columns. Balance uses PreviousRow() to compute. Assume duplicate dates exist in the data.
2. Decide (as I actually did), that having the most recent dates at the top is more useful. Sort table in reverse.
3. Now the Balance column “looks” incorrect, because it does not go in the expected reverse order when there are duplicate dates. The good news, though, is that it will still be mathematically correct and we won’t have a “Bank error in your favor, collect $200”.

In any case, for the discussion at hand I don’t think it is a deal breaker. Since PreviousRow(column) is defined when sorted ascending only (so how descending order sorts things does not matter). It will just be surprising to some folks when they do sort reverse.

Also, @shishir, seems you’ll want to have NextRow(column) as well.

Eric

[Piet_Strydom]

Somewhere along the line, somebody mentioned that this functionality is needed for “curbappeal” to new people that are looking for an Excel replacement.

They are not going to find this function, and they are not going to be able to understand and use it if they do find it.

Coda is not a spreadsheet replacement, and I doubt whether it will ever be. Access and Word - to a large extent. Excel, no.

So please don’t try and make it appear like a spreadsheet. The Verge article that put me onto Coda described it as a spreadsheet replacement. And it caused me a lot of frustration until I stopped looking for a spreadsheet replacement.

[Frank_Arnold]

If you have a filtered view the previous row is a different row as in the table, so it needs more some kind of “autonumber” based previous row, which counts the rows in the view of the table (filtered or not) and gets the previous row based on the autonumber

[Johg_Ananda]

To find the visual position of the row:

thisTable.Find(thisRow)

You can reference the above with:

thisTable.Find(thisRow)-1

[Paul_Danyliuk]

@Johg_Ananda

To find the visual position of the row:

thisTable.Find(thisRow)

Not true, because if the table is grouped, sorted, or filtered, this will no longer return the visual position but the position in the original dataset.

@Johg_Ananda

You can reference the above with:

thisTable.Find(thisRow)-1

This post of mine is a very old post. Now I discourage simply referencing the previous row and encourage pulling a relevant previous row through a thought out formula. It’s more often than not that the table for e.g. financial calculations would have entries for multiple users (in any order). In that case you definitely don’t want just the previous row — instead you want to filter for e.g. same user previous month entry.

[Axell_Padilla]

Hi, just checked, so we do have the “thisRow” reference and after reading about, it really feels “something is missing” when you look for previous and next. Obviously, there is a serious disadvantage of this formulas because you can end up with some performance problems needing to process almost everything when some related column changes.

I suggest, this easy formula:

• nextrow(table,rowference,filter expression,sort column 1, asc/desc, sort column 2,…),
• or, table.nextrow()
  then you can recalculate only if something of that filter partition (works like partition by in SQL) changes plus another optimizations.

Table=table to get next row from.
Row Reference=default thisrow, or select any unique row from another table.
Also, to simplify even more, you can enable an option to just get next visible row and make filters and sort optional (so you can just filter and sort the table).

That is almost equal to this formula assisted by just a rowid column to overcome duplicates:

ThisTable.Filter(
CurrentValue.Category=thisRow.Category)
.Sort(True(),ThisTable.Item)
.Sort(True(),ThisTable.Date)
.Filter((Concatenate(CurrentValue.Date.ToNumber(),CurrentValue.Item)>=
Concatenate( thisRow.Date.ToNumber(),thisRow.Item)
&&(CurrentValue.RowId!=thisRow.RowId))).First()

You can play around with this example that contains partitioning, duplicates and previous and next row formulas examples: