How to Average a range of records

Topic Labels: Formulas
3296 2
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

Hello! I am somewhat new to airtable so still trying to figure out some basics.

What I am trying to do here is take the average values of each categories per seven days and average them. Is this possible? I.e. I would like to take the measurement of weight in lbs from days 1-7 and average that giving the result in another column, then for 8-14, 15-21, etc . . . I want to do this also for the columns as well. So far I cannot find a way to do this.

Bonus: If it’s possible to specify this by the Date field every seven days starting on a certain day even better!

Screen Capture_select-area_20200826095401

2 Replies 2
10 - Mercury
10 - Mercury

Some quick thoughts - unlike excel’s cell by cell approach, Airtable runs its formula once per row. Meaning if you need to reference one row from another, you’re either using linked records or making use of a script instead of a formula.

Summary Bar
But you might be able to get away with grouping and the summary bar. This is the simplest solution.

Linked Records
Combine days into the same row together. Then using lookups and formula fields, you’d calculate their averages however suits you.

Scripting Block
Otherwise, you could use the scripting block (which would require you to learn some JavaScript) to create a more dynamic interface where you can specify which days you’re calculating together on an as needed basis.

If you are interested in the scripting block approach, I wrote a post about this here. This doesn’t calculate the average, but is very similar and could easily be adapted to show the average.