How to calculate the weighted average of 5 choices in a survey sent to multiple people?

Solution 1:

You can use SUMPRODUCT for this.

The formula to create the frequency table (example in cell H2):

=COUNTIF(A$2:A$10,$G2)

The formula in column N2:

=SUMPRODUCT(H$1:L$1,H2:L2)/9

If you wanted it to be more flexible to n, you could replace the 9 with a count of rows between 2 and the bottom of the data table.

enter image description here

edit:

More specifically (and possibly more accurately if you want the base to only be those who responded), you can use this:

=SUMPRODUCT(H$1:L$1,H2:L2)/SUM(H2:L2)