How to Score Weighted Assessments in Google Sheets

google sheets

I want to make an assessment tool in Google Sheets that asks a bunch of questions and provides a score. Each question has a set of possible answers, so they're all multiple choice. But each of the possible answers adjusts the score in a different way on a per-question basis.

Here's an example sheet with two tabs, one for the questions and one for the answer weights. The challenge I have is what to put in cells D2 and D3.

For example, "yes" might be worth +10 points on one question and -5 points on another. Similarly, one question may allow "yes"/"no"/"I'm not sure" and another may allow "yes"/"sometimes"/"no".

I'm not sure if Google Sheets contains the tools that'll let me do this in a reasonable way without resorting to writing App Script. It looks SWITCH might be useful, but I'm not clear on exactly how to wire things together.

Best Answer

Use following INDEX and MATCH formula:

=INDEX('answer-scoring'!$A:$G,
 MATCH($A2,'answer-scoring'!$A:$A,0),
 MATCH($C2,'answer-scoring'!$A$1:$G$1,0))

where index returns values from score table that matches both question and answer.

You can find it Here

Related Topic