How to average ratings from a student live online evaluation?

[Risto]

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:

[Raminder_Singh]

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

[Risto]

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!

[Raminder_Singh]

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?

[Risto]

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?

[Raminder_Singh]

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.

[Risto]

Thanks your help @Raminder_Singh!

This should work, I will test it

[augmented]

Hi Risto. I would just create another table of names (primary) and add rollup fields (use average) for each of the criterion in your ratings table. Should be straightforward. Good luck.

[Risto]

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