Calculating an average across rows

Topic Labels: Formulas
1786 1
cancel
Showing results for
Did you mean:
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!