Using Different Formulas on different Rows

[Brian_Bejcek]

Hello,

I am using airtable to manage a budget, I would like to be able to add the sum of numbers from one row, (as an ending balance for example) to a column in the row below it (as a starting balance for example). So I can see a running total.

The use case would be, my ending balance at the end of Month 1, is my starting balance for Month 2, so on and so forth. This way I can evaluate how much my ending balance will be in Month 6 if I add x amount to my budget in Month 1.

I have tried several ways, but end up with circular references. Is anyone able to offer some ideas or input on functionality I am missing to be able to accomplish this?

Thank!

[Jeremy_Oglesby]

These kinds of things are not easy to do in Airtable. Despite their advertising slogan using the word “spreadsheet”, Airtable is a database. “Cells” in Airtable have no spatial awareness of other “cells”, so you cannot do a lot of things that you can do easily in a spreadsheet, since you can’t reference other cells, or a set of cells directly.

There most likely are ways to do what you want to do, but they are probably not straightforward to achieve.

Here’s a good starting place in the forums to learn how to do those kinds of things:

Multi-record calculations (c.f. 'running balance,' 'row number,' and the like) Show and tell

tl;dr version: A frequent query and/or complaint concerns users who wish to access data from a record for use in a calculation performed in a different record from the same table. Often this takes the form of a user asking ‘how to retrieve a value based on row number’; other times the user wishes to perform a calculation that assumes knowledge of the previous record — for instance, maintaining a running balance. A recently published base contains four approaches to this problem, three involving …

[W_Vann_Hall]

And to what @Jeremy_Oglesby has said, let me add that I’ve not found any way to avoid circular references in applications like this other than by defining two fields in the record, one the formula (or rollup with aggregation formula) field that arrives at the calculated value (for instance, EOM balance), the other a number field that exists only to break the circular reference. So maybe you’d start with your calculated field arriving at March’s EOM balance. You’d next have to copy-and-paste the value from the calculated field into the reference-breaking data field. This second field would then represent the starting point for the calculations ultimately resulting in April’s EOM balance.

One of the entries in the link Jeremy provided includes just this sort of workaround, showing and explaining what to do. Keep in mind that a manual task can be a potential point of failure, as it will need to be performed every so often; however, you can use Zapier or Integromat (or similar) to automate the process, and you can build in alerts and other protection against failure.