Subtract value of just above and below row

It sounds like you want your table to act like a spreadsheet, but unfortunately it won’t. The default Grid view for tables makes them feel like spreadsheets, so I totally get where you’re coming from. However, database tables are operationally very different beasts. Field formulas can only see and act on data in the same record (row), not between records.

If you simply want to tally all of the daily count values, look a the bottom of your table. Assuming your count field is a Number type, it should default to showing you a sum of all the values.

What would be cool is if there were a “countdown” option in that summary area. It would be like an extension of the Sum option: it would add all the values in the column, subtract them from a starting value that you specify, and show you what remains. I can see that being a useful feature in many situations. Maybe I’ll toss that in the Product Suggestions category.

Sharing my solution here because I had the same question.

To subtract the value of another row within the same table from the current row in Airtable, you need to:

1) Create a “Link to another Record” column in your table.

• You’ll use this column to link to the record that has the data you need to perform your calculation with, in this case, the previous row. You could title this column “Previous Row.”

2) Create a “Lookup” column in your table.

• Select the column in your table that has the data you need to perform your calculation from the linked record in “Previous Row”.
• So, if the original column that has the data you need in your table is titled “Value”, you could title the new Lookup column “Previous Row Value.”

3) Create a “Formula” column that calculates what you need it to

• In this case, we’re calculating the difference between the current row and the selected row, so the formula would be: Value-{Previous Row Value}

The problem with this solution is that it’s a bit tedious in that you have to manually search for and add the record that has the data you need to complete your calculation, whereas in Excel or similar, you’d just create the formula once and then drag it down the column to the cells that you need to display the calculated result. Perhaps there is a formula that would automatically find and add the previous row, but I’ll let someone else figure that part out…

If I remember correctly, one of the example bases referenced in this post about multi-record calculations — because that’s what this is — automatically performs a calculation involving the preceding (and maybe the following) record. There are a bunch of caveats mentioned in the post I won’t repeat here, having to do with using autonumber or CREATED_TIME() to determine row precedence, and there is an essential piece of overhead (namely, linking every record of your main table to a single record in another table) required before any cross-record calculations can be performed, but it does give you a way to do such calculations without having manually to identify the records you wish to use.