Average Rating from multiple collaborators

[Howell11]

Hi,

I am pretty new to AirTable, so not sure if this is possible. However, I currently have a table for all users of my website and then a separate table for customer reviews. Each review populates a new row, so the same company may have multiple reviews, each with its own row.

I would like to input a formula to work out the average rating across all of the reviews for each company so I can then display this as an overall rating on their profile. Is this possible, if so if someone could help me with the formula that would be amazing.

[Mike_AutomaticN]

Hey @Howell11!

Glad to hear you are starting out with Airtable! You might want to check out this other post where a similar issue was discussed!

Also, below please find a screenshot of @kuovonne's amazing workarond! 

If you need any further help, feel free to reach out via Private Message or schedule a call using this link.

Mike, Consultant @ Automatic Nation

[Howell11]

Hi Mike, thank you for your reply. Unfortunately this formula doesn't seem to create an average from all the reviews for the same company. 

[Mike_AutomaticN]

Hey @Howell11, could you further describe your data structure?

The formula assumes that you have the following structure:
(i) Users table that corresponds to Companies.
(ii) Reviews table that is linked to Companies.
(iii) One Company can have multiple Reviews;
(iv) Rollup field on your Users table, rolling up the field for rating from the Reviews table. This rollup should include the formula mentioned by Kouvonne.

Please let me know if this still does not work -and you could send over a screenshot of the specific field where your are trying to obtain the average.

Mike, Consultant @ Automatic Nation

[Howell11]

Hi Mike,

Perhaps I haven't got the tables connected properly. I find getting the fields to connect correctly very confusing. I need the average rating field to show in the users table. Thanks for your help 🙂

[Mike_AutomaticN]

Hey @Howell11,

Do not worry. You can follow these easy steps (I'll start the example from scratch):
1. Create Users table
2. Create Reviews table
3. From your Users table, create a new field of field type "Link to another record.." and select the Reviews table.

4. On your Reviews table, create a new field called Rating, of field type "Rating"
5. On your Users table, create a new field called Average Rating, of field type "Rollup". For the rollup source, select the Reviews table. For the field you want to rollup select the Rating field. For last, apply the formula as shown on the image below.

IF(
  COUNT(values),
  REPT("⭐", ROUND(AVERAGE(values), 0))
)

Assuming this Reviews:

You'd get this average as a rollup:

You can find this example base on this link!

If rather than having stars for the average you'd like to have the number value, you can use this other formula:

IF(values, AVERAGE(values))

Hope the above is clear!

Mike, Consultant @ Automatic Nation

[Howell11]

Hi Mike,

Thank you very much, this has been very useful. I have followed your steps, and it seems my issues is that the linked record on the users table isn't automatically updating. If I manually add the users then the rollup works as expected. Do you know how to resolve this?

[Mike_AutomaticN]

Hey @Howell11! 

It is then a matter of how you are currently creating reviews. 

How are you currently creating Review records on Airtable? Via some form subimissions? If so, what form software are you using? Airtable native forme?

Mike, Consultant @ Automatic Nation