Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Base Design
- How to average ratings from a student live online ...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Topic Labels:
Base design

Solved

Jump to Solution

0
3809
8

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Feb 22, 2021 01:29 AM

Hi,

Could you help me calculate the average results (ratings) of a student live online evaluation?

I am testing 2 tools to collect the feedback of respondents, one is designed with Typeform and the other with Airtable. It is not possible to add a “multiple select” in the primary field with Typeform (“names” of students can only be added in the second column), while name can be put in the primary field with Airtable form. Does it matter to average ratings?

The evaluation goes as follows: The respondents select the name of the person to evaluate from a “multiple choice/select” list and rate them using "opinion scales/ratings). The answers are imported to Airtable at the end of the live session as you can see from the screenshots.

I would like to calculate the average of ratings for each group of names and use the value in the base. I noticed that the “group functionality” does not help as it is not possible to use/extract the average values in the same or a new table.

Do you have any idea how it could be done?

Thanks in advance!

Evaluation from Typeform:

Evaluation from Airtable form:

Solved! Go to Solution.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Feb 23, 2021 07:11 AM

8 Replies 8

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Feb 22, 2021 02:43 AM

Hi @Risto,

You can use the `AVERAGE`

function on the rating fields. E.g. if I have three fields Criterion 1, Criterion 2 and Criterion 3, then to compute their average I will write this in the formula fields: `AVERAGE({Criterion 1}, {Criterion 2}, {Criterion 3})`

Since the output of the `AVERAGE`

function is a number, you might want to display stars instead of a number. To do that use the `REPT`

function like this: `REPT("⭐", AVERAGE({Criterion 1}, {Criterion 2}, {Criterion 3}))`

.

If you use the above function you will see an error when the average is not an integer. E.g. if the average is 2.5 the `REPT`

function will fail because it doesn’t make sense to repeat something 2.5 times. So to fix that `ROUND`

the value: `REPT("⭐", ROUND(AVERAGE({Criterion 1}, {Criterion 2}, {Criterion 3}), 0))`

Finally, another case to handle is what happens when all of the Criterion fields are empty. In this case the `AVERAGE`

function will return NaN. To fix this you can assume a rating of zero for blank fields: `REPT("⭐", ROUND(AVERAGE(IF({Criterion 1}, {Criterion 1}, 0), IF({Criterion 2}, {Criterion 2}, 0), IF({Criterion 3}, {Criterion 3}, 0)), 0))`

If you want to extend this formula further, you can refer to the formula field reference here: Formula field reference – Airtable Support.

Hope this helps,

-Raminder

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Feb 22, 2021 03:52 AM

Hi @Raminder_Singh,

Thanks for helping!

Your solution averages each records. This value is not relevant for the students as different criteria are put in the same basket.

What is important is averaging each criterion for each name:

“Names” were evaluated five times so as an example for name 1 it should be ((`Criterion 1`

+ `Criterion 1`

+ `Criterion 1`

+ `Criterion 1`

+ `Criterion 1`

)/5).

So the questions I would like to answer is

- how to calculate the average of
`Criterion 1`

for name 1 - how to calculate the average of
`Criterion 2`

for name 1 - how to calculate the average of
`Criterion 3`

for name 1 - how to calculate the average of
`Criterion 4`

for name 1 - how to calculate the average
`Criterion 5`

for name 1 - how to calculate the average
`Criterion 6`

for name 1

and then the same for name 2, name 3, and name 4.

What would you suggest in this configuration?

Thanks!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Feb 22, 2021 04:05 AM

Oh I see. You are trying to calculate average for each Criterion field per name. That should be achievable by grouping by name and then using the average in the summary bar. Though the summary bar will only show a number not a star as I tried in my formula. You might also see a NaN if all the rating cells in a field are blank:

Is that sufficient for your use case?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Feb 22, 2021 04:51 AM

Thanks @Raminder_Singh,

Yes I tried grouping per name but it does not help as it is not possible to use/extract the values in a new table.

Do you have an idea how I could generate the average values so that they could be used in formula and other tables?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Feb 22, 2021 08:01 AM

In that case you can compute the averages in a script and then save them in a table somewhere. Something like this:

```
let table = base.getTable("Observation");
let queryResult = await table.selectRecordsAsync();
let groups = {};
for (let record of queryResult.records) {
let name = record.getCellValue("Name");
let criterion1 = record.getCellValue("Criterion 1");
var totals;
if (name in groups)
totals = groups[name];
} else {
totals = {"Criterion 1": {total: 0, count: 0}};
groups[name] = totals;
}
totals["Criterion 1"].total += criterion1 == null ? 0 : criterion1;
totals["Criterion 1"].count += 1;
}
for (let group in groups) {
let average = groups[group]["Criterion 1"].total / groups[group]["Criterion 1"].count;
//Save in a table or do whatever you want with the average
console.log(`Average for ${group} is ${average}`);
}
```

Note that the scripting app is available only in the pro plan.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Feb 23, 2021 06:50 AM

Thanks your help @Raminder_Singh!

This should work, I will test it :slightly_smiling_face:

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Feb 23, 2021 07:11 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Feb 23, 2021 12:18 PM

It worked and it was pretty easy to implement! Thanks a lot @augmented :slightly_smiling_face: