Help

Re: Balance / Sum of previous rows

2629 1
cancel
Showing results for 
Search instead for 
Did you mean: 
nealtz
4 - Data Explorer
4 - Data Explorer

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?

7 Replies 7
Cindee_Nielsen
5 - Automation Enthusiast
5 - Automation Enthusiast

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
6 - Interface Innovator
6 - Interface Innovator

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

@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
4 - Data Explorer
4 - Data Explorer

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?

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

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

Eddie_Ma
4 - Data Explorer
4 - Data Explorer

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