Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

How to calculate the average of a column?

19300 7
cancel
Showing results for 
Search instead for 
Did you mean: 
Lucas_Pereira
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi! How are you?

I’m new, and I’m having trouble with the average. I want to calculate the average of ao column that will ever grow (each day I’ll add new numbers). But when I create a new column to calculate it, it just repeat the numbers, instead of calculating the average number.

I need a field with the Average, to link this field to Dasheroo (and I’ll do that with other fields, to, like the highest rated item in the database).

How can I do that?

Thanks

7 Replies 7
Matt_Bush
Airtable Employee
Airtable Employee

It’s not possible to calculate the average of all the values in a column using a Formula column in the same table, since a Formula column value can only access other values in the same row, not values in other rows.

To calculate the average of all the values in a column, you have a couple options:

  • Use the Summary Bar, this is easiest to set up but note that it’s not currently possible to access these summary values values elsewhere.
  • Link all the records containing values you want to average to a single record in another table, and create a Rollup field in the other table to calculate the average of all linked records from the original table.
Lucas_Pereira
5 - Automation Enthusiast
5 - Automation Enthusiast

Thanks for the help. 😃

Elaine_Fabiano
4 - Data Explorer
4 - Data Explorer

Hi Lucus, did you solve your problem? I found a way to get the correct average without using a rollup. Kind of old school. In my Table I wanted votes counted and averaged based on a scale of 1-5. But voters also had an option to “Not Vote” or indicate that their votes was something “Other” or didn’t fall into the scale of 1-5. If their were a “no vote” or “other” the votes was counted as “0” or null. So when I tried to get the average the “0” was calculating in my Summary and then it would return an incorrect average - because the zero’s would lower the overall average. Here a couple of screen shots. (this is my first response/post - so I hope you can see the images).

So to work around this I used a formula instead. In layman’s terms it was like this:

IF the vote is valid - then divide the total number of votes / by the number of valid votes, and IF the vote was invalid (or 0) return a blank.

So then when I set my summary to give the average of the field it did it correctly:
Total votes (25) / total of valid votes (7) = 3.5714.

Hope you or maybe someone else finds this helpful.

~Elaine

SS of formula for average on PD.PNG

I’m not sure how you were able to create a rollup field that could access data from multiple rows. From everything I have tried, my rollup field was only able to access a single row. Is there a good tutorial to explain this specific aspect?

I’m wondering the same thing here. I have a simple 1-5 rating from a Survey Form in one column that I am looking to find the average of all the ratings.

Nikola_Radakovi
5 - Automation Enthusiast
5 - Automation Enthusiast

I am trying to get correct average with rollup. Is there a way to ignore the fields that don’t return any value (NaN) in the example?
Snimak ekrana 2021-06-17 131632

Use IF(ISERROR) functions in formula