Help

How to calculate a "winning streak"?

Topic Labels: Formulas
Solved
Jump to Solution
2222 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Marianna_Kerpp1
4 - Data Explorer
4 - Data Explorer

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=022c846bffe765f7904cc3124920d80...

1 Solution

Accepted Solutions
W_Vann_Hall
13 - Mars
13 - Mars

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.

See Solution in Thread

3 Replies 3
W_Vann_Hall
13 - Mars
13 - Mars

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.

Marianna_Kerpp1
4 - Data Explorer
4 - Data Explorer

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!

What’s the third-party tool you ended up using?