Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: How to calculate the average of a column?

7358 0
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

6 Replies 6
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