Subtracting rows in the same column?

Hey there,

I have a table where I’m tracking week over week tallies and want to display the difference in a separate column.

Screen Shot 2022-08-09 at 5.38.40 PM

I’m simply looking to subtract the amount in Row 2 from the amount in Row 1, but can’t seem to figure out how to isolate the individual cells to do this. I’ve found some documentation on how to subtract columns from other columns in the same table, but wonder if it’s possible to do this at the row level?

1 Like

HI Sasha,

Welcome to Coda! I hope you will get to love the product as well.

What you are asking is actually fairly complicated, for a few reasons.

  • The row sequence is not static, like it is in Excel. For a full discussion of row sequence, see here.
  • Formulas are valid for all rows in a column. Which means that if your formula is “difference = this row - previous row”, you are going to need additional logic to handle the case of the first row in your table.
  • Individual “cells” in Coda cannot be used in formulas like in Excel. The smallest unit to directly work with in Coda is a row.

But there are ways around this.
There is a special column type called RowID, see below. This stores a sequential number, generated as new rows are created. This number is unique. Even if a row is deleted, that number will not be used again.
You can then build a slightly more complex formula, which includes an If(), so that when the RowID=1, then Difference = 0, Else thisrow-previousrow.

In the end something like this should do the trick:

Regards
Rambling Pete

1 Like

Hi Sasha,

I liked my solution so much, I had to redo it in my book of Coda tricks. :wink:

Differences between rows · Rambling Pete's Quick examples.

On this page you will find a table where I have implemented this.

Regards
Piet

Hi @Sasha_Kai_Parker ,

While the Row Id works fine as @Piet_Strydom explains in the context of consecutive and no missing rows, an alternative approach you find below I use often in calculations of all sorts. The step 01 is the same formula as the Delta, but without the function Last() at the end.

The question is if you want the on the first row the complete number or a zero.

Alike approaches you find here:

Cheers, Christiaan

3 Likes

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.