Is there a way to create a moving average?

I think it would be possible by averaging only a certain set of data points, rather than all of them, but I’d have to see the rest of your base to offer any real help. Feel free to share the base through DM or a sample base and I’d be happy to take a look.

Long answer:

I guess it depends on which AVERAGE() function you mean. (Well, I guess it also depends on exactly what you mean by ‘moving average.’)

The AVERAGE() function used in a formula field will average whatever values you place between the parentheses, while AVERAGE() used in a rollup aggregation function will average the value found in all linked records.

In either case, what you are asking is one of the more difficult things to achieve with Airtable, mainly because of the lack of a persistent formula field — that is, a formula field that isn’t recalculated every time it’s displayed.

That said, there are ways of performing such a calculation. Which one you need depends on what sort of moving average you require:

1. One that is recalculated for each record added but retained as a record of the average at the time the record is created.
2. One that averages, say, the ten most recent values.
3. One that averages all values recorded on or after a certain date (for instance, in the past 10 days).

Also, if you could let me know how frequently you expect the values to be recorded (and with what degree of precision: ‘once a day, like clockwork,’ versus ‘once or twice every work day, usually, unless I forget’) and if you need historical values to be retained (that is, if you need to be able to go back and see what the moving average, however it is defined, was on last June 22, versus simply knowing what it is as of today), I can put together a quick solution for you. (Hmmm… note the question of historical values also affects how item 3 would be implemented. If you want an average of the last ten days, but then go on vacation for two weeks, when you open the base on your first day back, should you see the previous value or a blank field? Tomorrow, will you want the average of today and yesterday, or today, yesterday, and 23 days ago through 16 days ago?)

Short answer:

You’ll need to implement something along the lines of the solution given here — although probably a little more complicatedly.

Hi @W_Vann_Hall you mention above about 10 most recent values.

I have 2 tables - one with clients, one with results. One client has many results.

I would like to calculate an average of the three most recent results of a client. Is that possible?

Certainly!

Take a look at the routines I describe here and in the base linked from that post. You’ll probably want the approach demonstrated in the [ArbitraryDay] table; because it uses fixed-length values, it’s extremely easy to walk backwards through the list of measurements. You may also find some code to steal repurpose in the base linked from this post, which uses the [ArbitraryDay] algorithm to create an automatically updated Airtable Chart Block barchart graphing the most recent 10 entries.

Since you want to average the last three entries, your formula will need to extract values for the latest, latest-1, and latest-2 entries; the formula in {Chart::Orders} (that is, the {Orders} field in the [Chart] table) in the second base mentioned should get you started.

As always, the one caveat is that to make these routines work, every record in your source table (presumably [Clients], but conceivably [Results], depending on how your base is structured) must be linked to a single record in another table. I feel like I’ve described that process a dozen times in the past week, so if it’s not covered in either of the two posts referenced, look through the Support forum and click on threads with my avatar beside them; you’ll find one soon enough. (Note to self: Post a stripped-down description of that process that can be linked from other messages.)

Edit: I did: Instructions on linking every record of one table to a single record of another.

Last caution is to make sure the number of digits allocated for each logged result is enough to encompass your largest anticipated client result, including a minus sign if negative results are possible. IIRC, all of the code I’ve published so far has dealt with integer values, so if your results include decimal values, you’ll need to ensure your encoding routine — the formula that creates the key:value pair — zero-pads to the left and right as needed.

Let me know if you have any problems!