# Re: Need help with cumulative calculation

472 0
cancel
Showing results for
Did you mean:
4 - Data Explorer

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.

2 Replies 2
14 - Jupiter

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.

18 - Pluto

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.