Adding values from previous rows

[Jorge_Gamito]

Hi.

I have this table that I want to use as a money tracker
The goal is to list transactions updating the available ammount dinamically. So, given the initial value, in the rows above the “total” column should be updated.

It should be =thisRow.value + previousRow.value. Yeah, I know there is no such thing as previousRow. So how can I accomplish that?

Thank you very much.

[Daniel_Stieber]

Hey Jorge,
not sure if this is the shortest way, but at least it works:

How to do it:

• Add a unique consecutive Row Id to avoid problems when deleting / editing rows. You need two columns to make this happens, but you can hide them later:
• Add a new column and choose “Row properties → Row ID”.
• Add a new column, name it “Real ID” and use this formula:

=Rank([Row ID],thisTable.[Row ID],true )

• Hide the Row ID and Real ID Columns, if they annoy you
• Use this Formula in your Total Column:

=if(thisRow.[Real ID]==1,thisRow.value,thisRow.value+thisTable.Filter([Real ID]=thisRow.[Real ID]-1).total)

How it works:
It basically checks if you are currently in the first row.
If yes, it just takes the value of the value column.
If not, it adds the value of the current row to the total of the last row.
To do this, it needs a reference to the last row - thats why we need the Real ID

More about consecutive Row Numbers here:

Consecutive Row Numbers?

I know there must be a simple solution to this, but I cannot find it for the life of me. So, I need to have a column where each row has a unique consecutive number ranging from 1 to however many rows I have. I cant use rowId because if I delete any rows and then add new ones, the number will be wrong. Such a simple problem, but it is holding nearly all of my docs back from being made correctly

[tomavatars]

I feel that those workarounds are too much work for simple use.
I hope Coda think about simpler way to do this (why not a Row number based on the actual number and not ID of row?).

[Daniel_Stieber]

Yeah totally.

I think the row number vs. row id is one of the critical differences between a spreadsheet table and a database table. Both, number and id, makes sense in a lot of use cases, but I guess it is hard to combine them without confusing users.

[tomavatars]

Yes.
But users can already be confused for simple tasks. That’s my case sometimes, and I already have experience with Coda.
I’m afraid that those kinds of workarounds instead of straight comprehension would let new users disappointed. This is what I can read on productivity forums. People thinks that Coda is aimed for power users and devs.

[cnr]

@Daniel_Stieber @tomavatars I couldn’t agree more with you two on this. There is a critical need to introduce row numbers at the very least. It’s super inhibiting at times.

[Jorge_Gamito]

Hi, @Daniel_Stieber.
I really appreciate your reply. I’ll try this later.
Thanks.

[Jean_Pierre_Traets]

Dear Daniel,

When I studied your explanation I found one mistake in the formula, that could new users cause some problems to identify:

The correct formula is:
if(thisRow.[Real ID]==1,thisRow.Number,thisRow.Number+thisTable.Filter([Real ID]=thisRow.[Real ID]-1).Total)

In your formula you used “value” that doesn’t refer to the existing column header “Number”.

Still thanks for the creative solution, I doubt if I would come myself with this.

Amicable,
//JP

[Bruce_F_Donnelly]

This seems to work in the Total column too:

=thisTable.Filter([Real ID] <= thisRow.[Real ID]).Value.Sum()

[Jean_Pierre_Traets]

Dear Bruce,

Great to see that there are more creative ways to reach the goal.

Thank you sharing

Amicable,
//JP

[Yasin_Zahringer]

Hi all,

I am late to the party but I have the following idea:
A function which can be implemented from Coda is the cumulative sum function - it also fulfils Coda’s design requirements since it is not a cell based function.

All the best,
Yasin

[Mufid_Afif]

Any other workaround? It would be very simple if I can refer previous row and copy paste it like in other Spreadsheet

[Jean_Pierre_Traets]

Dear @Mufid_Afif,

Nice having you in our community.
If you want to “copy/paste” info from one to another table you could use a button that first filters out the data you want to extract and then places this to the table of your choice

This is one of the articles that references:

A Button to copy multiple Rows from one Table into Another

So I’ve got 2 tables I am using for a tracking document for my business. Because the larger a table gets (1000+ rows of tracked items) you get significant slowdown when adding any new rows to the table, I am employing a system when I move any already Invoiced items from the Master Table to the Archived Table. Imagine a setup like this. [Capture] With the help of some excellent Coda staff I’ve already figured out a way to set an automation to automatically move any rows that have their status s…

Many more great articles and support you will be able to find in this community

[Sarah_Gaillard]

omg this is so complicated for something that simple - indeed, it makes me upset and disappointed of coda! Is there any improvement for this topic in 2023? I just want to do a simple time calculation by cumulating several durations (event organization)… I can’t use coda for this right now, it’s almost half an hour I am looking for a solution and I don’t even understand all the variation of the “row” options in a formula…

[Piet_Strydom]

HI Sarah

I am sorry to hear your frustration.

Coda provides a much richer and more structured environment than what Excel does. Unfortunately that means that some things that are easy in Excel will not be easy in Coda.

And the previous/ next row topic is one of those.

To be able to have different views and different kinds of sequences the simple row sequence of spreadsheets no longer works.

Here is an explanation of the different kinds of sequences in a table (sort, entry and table order), as well as the method explained above by @Daniel_Stieber .

Yes, it is not as easy as Excel. BUT, it provides one with much more control over the sequencing of rows in a table, including multiple sequences on the same table. (E.g. a table of employees - you can set it up to work through the table alphabetically, as well as in Employee sequence.

Regards
Piet