Adding values from previous rows

#1

Hi.

I have this table that I want to use as a money tracker :slight_smile:
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.

table

2 Likes
Running Total (Sum, Average, Median, etc)
Income & Expense Tracker
Cumulative Start Times for Event Schedule
Running total column based on value in previous row
#2

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:

9 Likes
#3

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

1 Like
#4

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.

2 Likes
#5

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.

1 Like
[SOLVED]Formula within a single cell rather than whole column
#6

@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.

#7

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

1 Like
#8

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 :pray: for the creative solution, I doubt if I would come myself with this.

Amicable,
//JP

2 Likes
#9

This seems to work in the Total column too:

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

2 Likes
#10

Dear Bruce,

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

Thank you sharing :pray:

Amicable,
//JP

#11

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

#12

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

#13

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:

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