Comparing similarity of columns in a spreadsheet, and representing them as a %

Hello all,

I'm trying to score the spreadsheet like this. I want to compare Person P's answers with the answers of each other respondent. I'm counting any matches (either YES or NO) as 1 point, and any "PART"s as .5 points. A blank entry is not taken into consideration. So, at the bottom of each column, it displays the percentage of matching answers over total possible answers. Is there a formula that will do this automatically? Thank you all for helping me with this. I know it's probably a basic issue, but I know nothing about programming with excel.

Cheers!

You can put this formula in Q2:

=IF(P2="","",COUNTIF(D2:N2,P2)*IF(P2="PART",0.5,1))

then copy down to Q57. There are many ways of evaluating the percentage - do you mean this:

=SUM(Q2:Q57)/56/COUNTA(D1:N1)

formatted as percentage?

Hope this helps.

Pete

Originally Posted by martindwilson

well i make the percentages slightly different not all but some with in d59 filled across to n59

Formula:

Please Login or Register  to view this content.

This worked!-- Once I removed all the responses labeled "PART". I think those are going to be too difficult to calculate, and not really accurate if represented as a ".5" anyway, so I'll just take those out.

One more thing, how do I display it as a percent, rather than a decimal?

Originally Posted by martindwilson

format the cell percent!

Wow. That was a dumb question. haha, thank you! That was exactly what I was looking for.