Formula for averaging letter ratings/grades

[Jessica_Fox-Thi]

Hello,
I am trying to create an average based off of letter ratings.
For example, I have a table of individual’s projects which has a column with a grade or rating for that specific project. It can be A, B or C.
In a linked table for the individuals, I have a column which has all their grades/ratings in the one field. So one may just have an A and another may have B,C,B, and another A,A,B,B,A.
I would like to have a formula field which will take the group of grades/ratings for each person and averages them. I don’t care if the result is a letter average or a numeric average.
If I were a teacher, I guess this would be equivalent to getting each students GPA based off of just letter grade entries.
Each formula i try, i get an error…I feel like i’m missing something very obvious.
Thank you!

[Elias_Gomez_Sai]

You can’t do averages of letters, that is a mathematical concept. Maybe you can make equivalent numbers, even a Grades table where the name is the Grade itself, and a Field with the Rating as a number.

I’ve created a demo base: https://airtable.com/shr8Bjp5CtXbI5FJR

[Jessica_Fox-Thi]

Hi Elias,
Thank you for creating that. Unfortunately, I’m not sure if it helps with where we are now.
I used the students and grades as an easy example in the forum, but it is actually for rating auditions. If I was just creating my database in Air Table, then this would be much easier to use your example. But I’ve been using this for a couple of years now and I already have over 14,000 ratings recorded in the system. I would need to add that number to each one of those records.

I was also looking to see if I could create an If/then or a substitute formula to automatically translate one column of letters to a number as another alternative.

For either option, I would need to average each actor’s ratings.

I hope this makes sense. Who knows, I may have to just do the option you gave.

Thanks so much for your help!
Jessica

[Elias_Gomez_Sai]

Instead of a Grades table (and a Lookup field), you can insert your Grades values and then create a Formula to get the equivalent number. Then it’s the same.

If this does not helps, I think you need to explain your base in more detail :thinking:

[Jessica_Fox-Thi]

I’ll do my best to explain :confused:
I have a table with all the actors in it.
I have a table with all the auditions in it.
They are linked-people are linked in auditions table; auditions and ratings are linked into the people table.
For example, in the auditions table some of the fields are the linked person and their rating for that audition.
ACTOR (Linked/Actors table) ROLE (Linked/Roles table) RATING (single select)
[John Doe] [Major Bill Smith] [B]

In the ACTORS table, the RATINGs are in a lookup field from the auditions.
So if John Doe has multiple auditions, his RATING field in the ACTORs table could look like this, “B,C,B,A”

My idea, if possible, would be to have something that converts the letters (A=3, B=2, C=1). Then that converted field of numbers would have a formula that would average those numbers. So in this new field in the ACTORS table for John Doe would show a value of 2.

If it helps, I already have a field that counts the number of auditions for each actor.

I have a feeling I’m just going to have to create a new field in the AUDITIONS table and manually enter a value and go from there…

[Elias_Gomez_Sai]

Jessica_Fox-Thigpen:

have something that converts the letters (A=3, B=2, C=1)

Do that in the Auditions table! Add a Formula field like in my example, and change the lookup of the Actors table to a Rollup field, selecting the new Formula as field and Average as the function.

I’ve updated the base:

• Formula of the Rating in Auditions: IF(Grade = "A", 3, IF(Grade = "B", 2, 1))
• Formula of the Grade in Actors: IF(Average = 3, "A", IF(Average > 2, "B", "C"))

You can adapt to match your needs.

[Jessica_Fox-Thi]

Elias_Gomez_Sainz:

IF(Average = 3, “A”, IF(Average > 2, “B”, “C”))

OMG…This almost did it! I can’t believe it. So here’s where it’s taken me…
In the AUDITIONS table, I added the field as you suggested with the formula you suggested. I changed it a little to give a C a 1 and a blank a 0. I forgot i needed that.
In the ACTORS table, I added the field as you suggested with the formula you suggested. But for those actors who have multiple auditions and multiple ratings, they are all showing with the default of “C”.
Looks like this is the last hurdle!
Here are a couple of examples,
Actor ZV: Ratings shows “C,C,C,C,B,C,C,C”. Ratings #'s shows “1,1,1,1,2,1,1,1,0,0” (not sure what the 0 is for). then the Average field shows “C”.
It should show a .89 in the Average field. (the total of 9 divided by the number of auditions, 😎
How can i make that happen? Because it’s a lookup field, it’s not allowing me to do a COUNT or a ROLLUP as a field type.

[Elias_Gomez_Sai]

I’m lost :grinning_face_with_sweat:

You shouldn’t have those large strings “C,C,C…”, and “1,1,1.…”. The Rollup field in Actors table does the listing (internally) and average at once.

I’ve added data for “Actor ZV” in the demo base, and my Average field says 1.1 (9/8 = 1,125), and of course a Grade of C.

Also, you shouldn’t have the final “0,0”, I think you have some blanks processed by the formula.

[Jessica_Fox-Thi]

I got rid of the 0’s! But working on the averaging…I created another field in ACTORS and used the formula, AVERAGE({Rating #s}) but it’s returning NaN all the way down. I’m not sure what i’m doing wrong now that in the ACTORS table it has the long string. They’re not Rollups, they’re Lookups. It won’t allow me to select RATINGS from the AUDITIONS table under Rollups. I have so many tables and fields and records in my base. I’m sure I’ve overcomplicated the whole thing.

[Elias_Gomez_Sai]

Again, you don’t need to do a Lookup and get those long strings.

1. You make the conversion from letter to number in a Formula field in the Auditions table, let’s call it Rating.
2. In the Actors table, create a Rollup field (let’s call it ‘Average’) to the Auditions table, selecting the Rating field and AVERAGE() as the formula.
3. Create a Formula field in Actors table (let’s call it ‘Grade’), that converts the Average field number into letters.

This steps can be seen in my demo table. I don’t know what you are doing :man_shrugging: Why this is not valid?