Yes/No/NA Weighted Scoring System

Hello Experts,

I have a tricky score sheet I am trying to create, and I have been unable to find a solution.

I am trying to create a scoring sheet, with yes, no, na options. The tricky part is, if a specific question has n/a, I need the assigned weight of that question to be redistributed among the other weights.

For Example

Column A is the weight for that question.

Column A Column B Column C
20% Question 1 Yes/No/Na
25% Question 2 Yes/No/Na
15% Question 3 Yes/No/Na
10% Question 4 Yes/No/Na
30% Question 5 Yes/No/Na

So if Question 1 has the answer of Yes, it obviously receives 20% credit, If it is No, it receives 0%. If the answer to question 1 is n/a, I need that assigned weight of 20% to be redistributed among question 2 through 5.

The possible score should always equal 100%. However, if a questions answer is N/A - That question should no longer be included in the calculation, with that question's weight distributed to the remaining questions. There could be multiple questions with n/a.

The end goal, is to calculate a score based on the answers. So there would be a "Score" cell, that calculates the score of questions 1 through 5.

I hope I did not over complicate. I appreciate any assistance. (As an FYI, I can have the yes/no/na all in one cell with a drop down, or 3 columns, with each dedicated to a yes, no and n/a. I could then check mark the appropriate column)

How can the total always give 100% if there can be columns with a "No" value? I understand that if you put Na on Question 1 you then want to do (0.25*Question2+0.15*Question3+0.1*Question4+0.3*Question5)/(0.25+0.15+0.1+0.3)

But wouldn't you do the same thing if you put a no instead?

Or is putting a "No" basically giving you a value of 0?

Originally Posted by shg

	A	B	C	D
1	Wgt	Q	Ans
2	20%	1	Y
3	25%	2	Y
4	15%	3	N
5	10%	4	Y
6	30%	5	NA
7			79%	C7: =SUMPRODUCT((C2:C6 = "Y") * A2:A6) / SUMIF(C2:C6, "<>NA", A2:A6)

This works perfect. Thank you very much! Now to understand SUMPRODUCT and the formula itself. I will surely play with it some.

I have found a solution to this question. It's basically just the first half of the original formula:

=SUMPRODUCT((C2:C6 = "Y") * A2:A6)