I upload .csv versions of Google feedback forms onto Airtable as records. For each form someone fills out, they put in the name of Designer #1, score of designer #1, name of designer #2, score of designer #2, etc. to rate their quality.
The problem is, not everyone writes the names of designers in the same order. So Sarah (for example) could be designer #1 on one form entry and designer #2 on someone else’s entry. Therefore, if you take the average score of designer #1, it’s actually compiling the scores of several different designers.
I need to find a way, either through filtering or some other creative solution, to only get the records with designers of a certain name, and then average those scores. How do I do this when their names and scores are scattered across different columns?
Any ideas are appreciated, thank you!