Need help making a formula to keep a running balance

Airtable formulas only have access to data stored in fields on the current record. There is no concept of “the record above/below this one”. If you want to use information originating from another record the standard way would be to link the records together.

If you add a Link-type field for {Previous Transaction} you can add a Lookup-type field for the {Remaining Balance} field of the previous transaction. Then you could convert the {Remaining Balance} field into a formula that adds (or subtracts) the {Balance} field to the {Balance Lookup} field.

You could also do this with scripting but I would recommend linking records together. To quickly link all your records together, assuming all records have a unique primary field value and are sorted sequentially, you could copy the entire first column and paste it into the Link field offset by one row.

The problem with trying to create a running balance with linked record fields is that you will end up with a circular reference, which Airtable does not allow. There are ways to create a gap in the circular logic, and then have something that bridges that gap, but if you use automations to do this, you can quickly us up a lot of automation runs.

I recommend looking into why you really need a running balance, and then if a running balance is absolutely necessary, use scripting.

That’s true, this method runs into a circular reference (I forgot about it because logically it isn’t a circular reference, but is one under-the-hood).

As mentioned an automation could copy the {Previous Remaining Balance} lookup into a plain currency field and that will resolve the formula’s error but the number of automation runs to sustain this setup would add up.

Then there’s following non scripting, non automation methods as covered in these threads
Simple-ish:

OG Solution:

Another option requiring basically doubling the size of your base, indefinitely

As you can see from those threads getting a running total is harder than it sounds.

I would agree. You currently have employees as a single select field. Would you benefit from having a table for employees and simply seeing the total balance for each employee as opposed to a running total on every transaction?

@Steve_Madar Notice that all of those solutions were created before Scripting existed. In fact, one of the example scripts in the scripting documentation is actually for calculating a running total. It is a simple enough script that you may be able to get it to work by simply changing the table, view, and field names to match your base. However, this script takes a long time to run when you have a significant number of records, and it is not suitable for running as an automation.

Also none of the formula/rollup solutions scale well to large bases. Having rollup fields with many linked rcords will slow down a base. And some of the solutions have limits on how the data is structured.