SOLVED

complex questionnare in excel

Copper Contributor

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:

1A0.5
 B1
 C1.5
2A0.75
 B0
 C0.25
 D0.05
3A1
 B2


would appreciate any inputs.

2 Replies
best response confirmed by VK01852395 (Copper Contributor)
Solution

@VK01852395 

I'd repeat the question numbers, and convert the range to a table:

HansVogelaar_0-1718480492347.png

I named the table Scores.

You can then use an INDEX/MATCH formula:

HansVogelaar_1-1718480550865.png

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.
1 best response

Accepted Solutions
best response confirmed by VK01852395 (Copper Contributor)
Solution

@VK01852395 

I'd repeat the question numbers, and convert the range to a table:

HansVogelaar_0-1718480492347.png

I named the table Scores.

You can then use an INDEX/MATCH formula:

HansVogelaar_1-1718480550865.png

The formula in C2 is

=INDEX(Scores[Score], MATCH(1, (Scores[Question]=A2)*(Scores[Option]=B2), 0))

This can be filled down.

 

View solution in original post