Field containing numbers - how to calculate a running average

Airtable is structured more like a database than a spreadsheet. As such, records (rows) can’t arbitrarily reference cells from other records. They can only reference their own cells. For example, if you have {First Name} and {Last Name} fields, and want to make a formula to combine them, you don’t have to specify what record Airtable should grab the names from. The first record will only grab data from the first record, the second record will only grab data from the second record, and so on. In short, each record is an independent entity, and doesn’t even know of the existence of other records. Calculating a running average can be done, but not through the same methods that you’d use in a spreadsheet.

One method would be to make a new table named something like [Summary], and delete all but one record. Add a link field to your [Scores] table (guessing the name), and link all records to this lone record in the [Summary] table. (An easy way to do this: make the link in one record, then drag-fill that link into all the other records.) Assuming that you have a date field in [Scores] recording each day’s date (I’ll guess this field is named {Date} for now), add a formula field named {Past Week} with this formula:

DATETIME_DIFF(NOW(), Date, "days") <= 7

That will output a 1 for all days within the last 7 days, and a 0 for all others.

Back in the [Summary] table, add a rollup field. Rollup fields perform calculations on data found in linked records—in this case, the linked records in the [Scores] table. The field to roll up will be the one containing each day’s score. The aggregation formula will depend on what you want to do with those scores. For a simple total, use SUM(values). For an average, use AVERAGE(values). To see the highest score, use MAX(values). There are lots of other options; play around and see what meets your needs. You can always make multiple rollup fields, each with a different aggregation formula, to track/calculate different stats.

The final thing to do in this rollup field will be to turn on the switch under the field selector that reads, “Only include linked records from the Scores table that meet certain conditions”. For the condition, set it to pick records where {Past Week} = 1. That will ensure that only those records from the past seven days will be included in the calculation.

You can also then set up an automation to take a ‘snapshot’ each week and add a record in a table set up for logging. I use similar method to keep a log of quarterly averages. I have an automation set that is triggered at start of each new quarter to grab the current number and put in a log table.