Creating a score for consensus and divergent responses in a table

[serendipiteez]

Hi there,

I am looking for a way to rank responses to a survey based on the degree of alignment. I am having trouble coming up with a formula for this. I want to rank the table from most consensus (agreement) to least agreement (divergence) based on a computed score.

Basically, my sheet has four categories (category 1, category 2, category 3, category 4) and 10 questions. Let's assume that 100 people answer the survey.

	CATEGORY 1	CATEGORY 2	CATEGORY 3	CATEGORY 4
Question 1	100	0	0	0
Question 2	75	25	0	0
Question 3	50	50	0	0
........
Question 9	50	30	20	0
Question 10	25	25	25	25

You can see that most people have put their responses in Category 1, so there are all in agreement (alignment), so this should get a score of 100. In Question 10, people were quite divergent in their opinions, and it's equally distributed across different categories, so this should get a score of 0.

How do I achieve this through a formula?

My objective to understand where people agree and where they don't. I have tried std.dev and other formulas for dispersion, but have not had any luck.

Thanks very much.

 

[jmacleary]

Hi there. You don't seem to have given enough information about the 'in-betweens' to be able to offer a solution. For example, q2 has 2 categories answered with different quantities - what would you expect there? 0.75, 0.5, 0.6667? I'm thinking maybe something along the lines of averaging the totals and a calculation based on the number of different categories answered?

 

[serendipiteez]

Apologies for that. I imagine that Question 2 might be 0.75 or 0.67. Question 3 would likely be a score of 0.5 (or 50) depending on the scale we choose to use. So it can be either out of 1 or out of 100.

I am open to different approaches.