Need help with cumulative calculation

[Eugene_Gershtei]

I apologize if this has been discussed previously, but I couldn’t find anything similar in my search.

I have a base which represents, essentially, a weekly golf tournament. There’s a table of golf rounds, with each record associated with a week and a player. There’s a formula field that calculates that round’s deviation from the average score from all the players that week.

What I need to do is have a calculated field in each of those recirds which represents the player’s handicap for that round, which is the average of all of those deviations for all of the previous weeks, but not the current or later weeks.

Is there some way to accomplish this?

Thanks.

[Jeremy_Oglesby]

This will require some work in the Players table.

Presumable you can see all of a Player’s linked Rounds in the Players table. So you would be able to Rollup the {+/-} field in the Players table with AVERAGE(values).

But now there’s 2 tricky parts - 1. Not counting the most recent Round, but more importantly, 2. Avoiding a circular reference error, which may make this whole thing moot…

I’d try the Rollup I described above first, and then see if you can pull that value back into the Rounds table and apply it to the Round Scores (which is what I presume you intend to do) without a circular reference error.

[Justin_Barrett]

In addition to the possible circular reference mentioned by @Jeremy_Oglesby, I see another issue: the number of values that you want to use in the average calculation isn’t consistent. It changes each week, and is likely different between some players. There are ways to feed only specific values into a rollup calculation, but it requires a formula field to act as a filter to determine which values to include, and that formula field is what’s rolled up. Because the number of values in the handicap formula changes weekly, and you don’t want later weeks to impact earlier ones, you essentially need one filter formula and one rollup field per week per player. That’s a lot of overhead to mess with.

Frankly, I don’t see this happening strictly inside of Airtable. An API solution would likely be better, as it would probably be far easier to do all the parsing and filtering in actual code, and then store the resulting handicap value back into Airtable.