I have data that was generated from a Google Form. The data has two parts: first a list of items the recipient checked, and second a score they gave (1-10). I would like to find the average score per item that was checked.
Here is some sample data:
Ultimately I want the result in this form:
If I had a temporary table that looks like this:
then I would be able to compute my final answer. I used this answer to write the query
=QUERY(E2:F10, "SELECT E, AVG(F) GROUP BY E LABEL E 'Reason', AVG(F) 'Average'")
I'm able to create almost what I want, but not quite. This is the closest I've gotten:
Which I can get using:
You can find all of this sample data here.
Would you please help me, either by transforming the data to be in the form I ultimately want it in (with the averages), or by helping me to create the temporary table in between?