How to calculate the average of a column?


#1

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


#2

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.

#3

Thanks for the help. =)


#4

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


#5

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?