Balance / Sum of previous rows

[nealtz]

I can’t find a solution for this in my opinion very common task: I want to get a balance over rows.

I.e. if I track working hours, I’d like to have a field in the row with the sum of all previous working hours till that day.

 Date  :  Work : Balance
       :       :
01.03. : +3,00 :  3,00
02.03. : +3,00 :  6,00
03.03. : +2,00 :  8,00
04.03. : -3,50 :  4,50
05.03. : +4,00 :  8,50

I can’t find a formula that allows me to address a special field in the row above the actual row. Is there a solution for this in Airtable?

[Cindee_Nielsen]

I have a similar need. I am tracking expenses and want to keep a running balance, subtracting expenses from a budget amount so each line gives the running available balance. Hopefully my comment will help someone respond to yours and we can both get an answer.

[EastEndTom]

Hi there, I too am looking for this and can’t really believe that it isn’t possible. Essentially -1 operation to the row above. Can anyone help, please??

[W_Vann_Hall]

@Cindee_Nielsen, @nealtz as well…

Unlike in a spreadsheet, row order and relative positioning carry no significance in Airtable, so the row-based formula definitions you’re looking for don’t exist. That said, it is possible to calculate running balances, day-to-day variation, and the like: Instead of an implicit link based upon row position, you simply need to find a way to create and follow explicit links tied to the record ID. A few days ago, as a reply to a post with a very innocuous title, I posted a discussion of one method of implementing a running total or tracking day-to-day change with Airtable. That post (and, now this one as well) also contains a link to an example base demonstrating this approach.

Hope this is of some assistance!

[achen]

Thanks for sharing your method. This seems like an incredibly complex way to do something that would be a simple expression in excel. I understand that Airtable is not really excel (just mean to look like it), and we’re dealing with a database, but even in a DB, you could query a previous entry. There seems like there should be an easier way to do this. Anyone else figure this out?

[Andy_Lin1]

There hasn’t been any changes in the Airtable interface that would allow that, unfortunately. I believe Fieldbook (which is a different site entirely) allows you to do a database search based on a value (so something like {Date} -1), but the syntax is quite different from Airtable (that is, entirely unlike Excel).

[achen]

Thanks. Has anyone submitted this as a feature request to Air Table? Seems like something worth asking for…

[Eddie_Ma]

I don’t know if anyone else has worked out this work around. Basically i am able to get a cashflow schedule working by:

1. Creating a new field in the table that LINKS THE CURRENT TABLE
2. Then select each entry prior to the current entry manually
3. Create a roll up field of the totals of the previous entries plus the current to get a running total.

Its a bit manual but once its set up you can copy and paste the previous entry and add the current entry.

Hope this helps