How to calculate a "winning streak"?

Hello! I would like to calculate a ‘winning streak’. Each row has the result of a game with a column for wins/losses, as well as a column for the dates of the games. I would like to calculate a winning streak. In Excel, I would do this with a self-referencing formula: IF({Win/Loss}=“Win”,{Win/Loss}+1,0), How might I do this in Airtable? Thanks!

Here’s a sample table: https://airtable.com/invite/l?inviteId=invEhMSt3pM1PhMUr&inviteToken=022c846bffe765f7904cc3124920d800b61f879f25b7d41b5030bfabcc64b7bb

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.

2 Likes

Thank you for your comprehensive post! Also for the nudge to use a third-party tool to perform the calculation. Turns out that was the easiest option! Thanks again!