SOLVED

# complex questionnare in excel

Copper Contributor

# 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.

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

# Re: complex questionnare in excel

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.

# Re: complex questionnare in excel

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

# Re: complex questionnare in excel

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.