Yes, it is possible — but not in the same manner as with a spreadsheet.
Your post, along with a few others recently, started me thinking about Airtable and spreadsheets. I’d often wondered if the spreadsheet metaphor might not be a double-edged sword for Airtable. Clearly, the familiar rows-and-columns interface makes it less threatening for newcomers to give it a try — but the application’s insistent refusal to behave like Excel undoubtedly turns some away.
But now, though, I’m starting to think, rather than the spreadsheet metaphor, the larger issue is the spreadsheet as metaphor. One of the things that makes spreadsheet software so easy to learn to use is that often relationships among cells can stand in for relationships among the things the cells represent.
For instance, in the example you gave, you mentioned a hypothetical spreadsheet in which cell B2 represented today’s closing price, and B1 represented yesterday’s. Now, the spreadsheet didn’t care which two cells you chose; they could just as easily have been A32 and YY917. For the user, though, there’s an implicit relationship between B1 and B2, one that speaks to precedence and sequence. (For instance, you could just as easily have used B1 for ‘today’ and B2 for ‘yesterday’ — but B1->B2 makes more sense visually, with B3 the logical choice for ‘tomorrow.’) Once the user has mastered how visual relationships map against those in the real world, it becomes almost second nature to use or enhance the spreadsheet. It is a genius bit of interface design — and it is nearly as hard to forget as it was easy to learn.
It’s important to keep in mind Airtable’s similarity to spreadsheets is almost entirely visual, rather than logical or functional. (The functional similarities that do exist are all at the UI/UX level – for instance, click-and-drag to duplicate records or values.) Logically, though, Airtable — as currently implemented — is all database. That Row 3 follows Row 2 and precedes Row 4 is merely chance; nothing can be inferred about the relationships among those three records.
It must be noted this is a strength as well as a weakness. True, one loses the ability to define relationships through visual position that spreadsheets offer; on the other hand, functionality and data integrity are no longer so immutably bound to screen layout. Who, when working with spreadsheets, has never risked losing hours of work with a poorly timed ‘sort’ or arrived at an invalid conclusion when formulas in newly added rows were either missing or contained incorrect references? With Airtable, on the other hand, while one must make explicit the relationships among records, the integrity of the base no longer depends on the order in which records are displayed. This allows for functionality, difficult if not impossible to achieve with a spreadsheet, based on the ability to present and aggregate the same data across a variety of different views.
Unfortunately, in its current incarnation, Airtable doesn’t make it easy to define relationships between records or to define persistent values. (A ‘run once’ tick box on a formula would make things much easier… while, no doubt, sowing additional complexity and confusion at the same time. Sigh.) However, there are ways to do so.
Case in point: I’ve tossed together an example base to demonstrate one possible implementation of the functionality you described.
The first three fields of the ‘Daily Closing Price’ table — {Date}, {Price}, and {Change in Price} — came directly from your post. To these I’ve added a handful (hmm… 6, actually; would that be a “baker’s handful?”) of additional fields. {DateCardinal} and {PreviousCardinal} establish the cardinal day value of {Date} and the most recent prior trading date, which I define as the Monday through Friday, previous. {DatePrice} is simply {DateCardinal} and {Price} concatenated with a colon (’:’) as separator.
The most essential added field is {Aggregate}, which is a linked record field linking each record in the DCP table with the single record in Aggregation. Not only does the latter table contain only one record, that record contains essentially only a single meaningful field: {DailyPrices}, a lookup of all {DatePrice}s from DCP. The Aggregation table is needed only because Airtable presently does not support a lookup or rollup of values from within the same table; accordingly, here {DailyPrices} aggregates daily price data from DCP to Aggregation, and {AllPrices} performs a complementary lookup of the aggregated values back to DCP.
The last additional field in DCP, {PreviousPrice}, scans {AllPrices} in search of a closing price recorded on the day specified by {PreviousCardinal}. If one is found, the amount is extracted; if not, a blank value is returned. Finally, if the value of {PreviousPrice} is not BLANK(), {PreviousPrice} is subtracted from {Price} and the difference returned in {Change in Price}.
Admittedly, that is a bit more complicated than B2 - B1 — but, honestly, it is far easier to look at and understand than it is to describe. The only significant difference in its use, as compared with a spreadsheet-based implementation, is that an explicit link to Aggregation must be defined for each new DCP record. Fortunately, given there is but a single record in Aggregation, to do so only requires two mouse clicks: once on the plus sign (’+’) in {Aggregate}, to add a link, and a second time on the Aggregation record itself. (As is my habit for such links, I named the Aggregation record ‘ :white_check_mark: ’, the White Heavy Check Mark emoji. This allows for quick visual confirmation in DCP that each record is properly linked.)
Take a look at the example base and see if it makes sense. (As you can’t examine the configuration of cells in a read-only base, I’ve documented each field using the field description, including the formula used. Alternatively, you can copy the base, which will give you full access to examine or change any field.)
Finally, notice the row order. The first five records I entered in sequence, as one would in a spreadsheet. The next five records, though, are for dates preceding the first five; they are also entered in random order. Since the algorithm does not depend on row order, the calculated values are still correct. In addition, when I entered the record for September 8, the ‘Change in Price’ field in the first row, which had been set to BLANK(), was automatically updated to the correct value.
