Here, we call those “multi-record” calculations — a.k.a. a pain in the neck. Currently, Airtable has no intrinsic support for performing two-dimensional calculations — that is, ones that involve values from more than a single table row.
Also unlike a spreadsheet, the physical relationship between two records carries no implicit meaning. Airtable assumes records can be presented in any order, with the sequence chosen most appropriate to the given view and filters. In many ways this is superior to a spreadsheet, as each record carries with it meta data that places it at a specific time. In contrast, your Excel example loses all meaning should the rows be sorted by an arbitrary value.
The challenge, then, is finding ways to access this meta data and relate it to the desired fields, given Airtable’s current limitations. Right now, to do so entirely within Airtable — that is, to do so without resort to a third-party integration tool like Zapier — requires some finagling. This post and the demo bases it references give you some examples of how to do so within Airtable; the running balance example is particularly applicable.
Note you’ll have to take a few steps to get around Airtable’s circular reference check; in brief, Airtable does not allow an self-referencing formulas, even those that structurally do not result in a loop. The running balance demo includes a workaround to avoid this which should be easily modifiable to support winning streaks.
