Help

Calculating an average across rows

Topic Labels: Formulas
2179 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Simon_Dennett
5 - Automation Enthusiast
5 - Automation Enthusiast

Hey Everyone,

Would love some help with something from the forum! I feel like this is something I should be able to do but am stuck!

I have a table in a base that simply put a user enters a score from 1-5 (dragged in from a survey). The table can have multiple users and each user may enter that score multiple times eg once per week for a year.

What I am actually trying to do is track a users sentiment toward something over time.

So it basically the table looks like this:

UserID : Date of Entry : Score

User 1 : Monday : 2
User 2 : Monday : 4
User 1 : Tuesday : 3
User 2 : Tuesday : 3
User 1 : Wednesday : 4
User 2 : Wednesday : 1
User 1 : Thursday : 5
User 2 : Thursday : 1

Thats all fine but what I want to do is do is add a third column to the calc that shows a rolling average for each user sentiment over time. So in the above example User1 sentiment goes 2,3,4,5. If 5 is good and 1 is bad that means their sentiment is improving over time.

So the calc I would like to do for each user is to say sum(last 3 sentiment records)/3 to give an average, so I can give a general indication of the moving average.

Is this something I can do in airtable, it feels like it should be easy but just can figure out how!

Please help - and sorry if it is a really dumb question!

Simon

1 Reply 1

Welcome to the community, @Simon_Dennett!

This is relatively challenging to do in Airtable, because Airtable has no concept of other rows — unless they are linked records from another table that you have linked to your current record.

A quick way of doing this is to group your records by User, and then use the summary bar to show the average of each user group.

But that wouldn’t limit you to just the last 3 records per user. That’s significantly more complex, but @Justin_Barrett has documented a way to do that here: