Formula to help calculate % score based on different criteria

[Rob0424]

I am trying to figure out how to calculate a weighted % score based on criteria that is different. In columns G:T I have questions with Yes, No or N/A as selection options, in column F I have a 1-10 rating system. In row 1 I have entered %'s to weight the questions, totaling 100%. I'm trying to create a formula that will take all the "Yes" responses and calculate a % Score, which currently works:

=SUMPRODUCT((G5:T5 = "Yes") * $G$1:$T$1) / SUMIF(G5:T5, "<>N/A", $G$1:$T$1)

But now I want to add another criteria of using the rating system to factor into that % score as well. So If someone scores 8-10 it is good, 6-7 it is OK and 0-5 it is bad. Based on this it will adjust the % score being returned by the formula above. Any ideas?

 

[BigBeachBananas]

Thanks but I'm looking for something that uses the 1-10 rating a bit more in the calculation. Appreciate your idea though. If I can't figure something out I will take that into consideration.

You're looking for an objective answer to a subjective question. Again, there are more than 1 method. Pick one and back it up. That's all the help I can offer. Try post your question on a math forum. Once you figure that out and still need help with the Excel part, will gladly help.