How can I find average value of a number in a user specific column

Ask for Help
1

Hi, I have a table where I want users to give themselves a score or rating against a number of questions, score will always be in the range 1 to 5.

So i have a table with 1 row for each of the questions and a user specific column where the user can enter their rating. This works ok and each user can record a rating for each question

I would love to be able to see the average score/rating for each question. I thought I could maybe do it using a rollup on the user specific column but that doesn’t seem to work. I would like the users to see how they compare to the average and I would also like admin to able to view and report on the average ratings for each question.

Any help or comments would be greatly appreaciated.

2

use the rollup column with average option:

Screen Shot 2022-03-15 at 3.28.33 PM
Screen Shot 2022-03-15 at 3.28.33 PM590×892 42.4 KB

1 Like
3

If you are looking for an average of all the users ratings then what you could do is add 5 more basic columns to your sheet that are ‘global’. (Rating1Star, Rating2Star, etc…) Increment those new global columns, in addition to your user specific ones, every time a user submits their response. Finally rollup your new columns.

For the user’s own average you can follow @Uzo’s example above and do the rollup on the user specific column. You can’t however see each users average separately in an admin view. This is because user specific calculations only occur on that specific users device and are not accessible to anyone other than that user.

One workaround to seeing individual averages would be to login into the builder and ‘preview as’ each of your users one by one. Probably not what you’re looking for but it’s something to think about.

3 Likes
4

To echo what @Eric_Penn said, you would have to build in some more logic to calculate the average. Something like this.

Obviously, it would be a whole lot easier to have a row for each user. Actually, if you have user profiles enabled, then you could just reuse the existing user profile table, display the screen based on the user table, and store the results on each user row. That way you can use a normal rollup → average on all rows in the table, without trying to figure out how to pack multiple results into one row.

Otherwise, you’ll have to build some sort of submission process that would update a total and a count column, but you would also have to account for any time that a user removes their rating or updates it, which means you would have to remove and recalculate the totals based on the old rating before updating with the new rating.

5 Likes
5

Thanks for the responses here, sorry for late response, I hadn’t been able to look at this in the last week.

1 Like
6

I sorted this out and thought I would post it here in case it helps anyone else.

I now have a form to allow users to answer a question (answer always a number from 1-5) and store this in a user specific column in the Questions table.
I then created an action to also send a Webhook to Make.com when a users submits their answers, which starts a process of adding the question and answer to a separate sheet that stores all of the answers for every user.
If a row already exists for that user/question, then the answer is updated, so the Answers sheet only contains one row per user/question
I then have a rollup in the main Questions table which pulls the scores from the Answer sheet and allows me to get the average whilst also having the user specific column.

Works very well, apologies if the explanation is confusing

1 Like
7

That’s a good structure, the only downside is rows count.