Running total column based on value in previous row

[Nick_Holt]

I’m trying to keep a running total (headcount) in my example:

As you can see, the result for every row, except the first, seems to be some sort of cell identifier (as text) with the movement value added (again as text).

I’ve seen adding-values-from-previous-rows but the formula format using square brackets doesn’t seem to work.

Is it possible to write a formula that takes a value from the previous row?

Additionally, it seems that when using nth you only get back the ‘display value’, which in my example is OK, but it would be really nice if there was a way to refer to a specific column.

[Lloyd_Montgomery]

Hi @Nick_Holt,

Here is what I came up with:

initialHeadcount + Headcount.Filter(CurrentValue.[Row ID] <= thisRow.[Row ID]).Movement.Sum()

This does, however, assume that your list has been created in order. If you add a row in the middle, the formula no longer works.

I’m working on a formula that doesn’t care about added order. I’ll let you know if I come up with something.

PS - The “Row ID” column appears to need to exist for this formula to exist. Just create it using the “Row Properties” -> “Row ID” column type, and then hide it. Apparently, you cannot get the RowID of CurrentValue.

Lloyd

[Ander]

@Nick_Holt @Lloyd_Montgomery

This seems to work, despite the “Wrong argument type” message in the [visual row position] column formula.

[Lloyd_Montgomery]

Hi @Ander,

(Excuse the edit… In looked too quickly)

That is brilliant! I couldn’t figure out how to find the index of the row we were on, and you did it… Well done.

Here is my alternative solution with the formula from Ander integrated:

Note: I imagine the solution from Ander is in fact better on performance, since mine calculates a sum across all previous rows, for every row. (But mine doesn’t have an “If”) Ander just calculates based on the last row. Without knowing more about Coda’s implementation, I can’t say more.

Lloyd

[Ander]

@Lloyd_Montgomery

@Lloyd_Montgomery

Adding a row you populate yourself

I edited that post twice, so just to clarify: [visual row position] is a formula, not manually populated.

[Lloyd_Montgomery]

@Ander Yeah I edited mine as well after I realized I reacted too quickly. Brilliant solution. I will definitely be using this in my future formulas.

Lloyd

[Ander]

@Lloyd_Montgomery

Credit to @Krunal_Sheth. I got the idea from his post here: Visual Position of the row in table

[Lorraine_Becker]

I have tried these solutions out and unfortunately I run into calculation problems… there is just not enough fire power in the browser to do run these sort of calculations.

[Jean_Pierre_Traets]

Dear @Lorraine_Becker

I am sorry to understand that you face calculation issues in your doc.
As you know, there is a lot of know how in the community, and if you can share a dummy sample doc most probably suggestions to help you out will see daylight