Forum Discussion
complex questionnare in excel
In an excel, I am having a quiz, but complication is that each question has different options and hence difference score. So each question can have one answer and based on selection, there is a score given which is different for each question options.
So for example For Question 1 - selecting A is .5, B is 1.0, C is 1.5 and so on.
and Question 2 could be A is .75, B is 0, C is .25.
This should give the idea of the complexity. I tried combination of vloopup and countif etc but not able to wrap my head around it. At the end, i just need total for all questions. e.g. Q1 - .5, Q2 - .25 = .75.
ref:
| 1 | A | 0.5 |
| B | 1 | |
| C | 1.5 | |
| 2 | A | 0.75 |
| B | 0 | |
| C | 0.25 | |
| D | 0.05 | |
| 3 | A | 1 |
| B | 2 |
would appreciate any inputs.
I'd repeat the question numbers, and convert the range to a table:
I named the table Scores.
You can then use an INDEX/MATCH formula:
The formula in C2 is
=INDEX(Scores[Score], MATCH(1, (Scores[Question]=A2)*(Scores[Option]=B2), 0))
This can be filled down.
I'd repeat the question numbers, and convert the range to a table:
I named the table Scores.
You can then use an INDEX/MATCH formula:
The formula in C2 is
=INDEX(Scores[Score], MATCH(1, (Scores[Question]=A2)*(Scores[Option]=B2), 0))
This can be filled down.
- Worked like charm! You made it simple for me. I learnt a think or two. Thank you very much.
