Accounting for Statistical Weights with Ranking Data

[liquidmettle]

Hello Everyone!

I don't know if there is exactly a correct place to ask this question on this forum, but I'm a little stumped so I turn to you.

I conducted a SWOT Analysis (Strengths, Weaknesses, Opportunities, Threats) at my organization. From a set of different focus groups, the feedback for each of these categories was compiled into a single list.

This list was then released via survey monkey where staff from across the organization were asked to rank their top 7 priorities for each category. The choices were weighted (such that the "1st priority" had a weight of 7, and the "7th priority" had a weight of 1).

Respondents could only choose 7 items, and each item had to be a different rank (1-7) (this was controlled through survey monkey, so that it was not possible to rank two items number 1, etc.).

The dilemma I've encountered (after only 35 responses- I expect many more next week), is that if a single person rated a priority as their top priority, the average weighted value would be 7. But if 20 people rated a different priority as being their 1st to 3rd most important, the average weighted value will be somewhere between 5-7.

This makes Priority 1 look more important from the weights, but the fact only a single person thought it worth rating indicates its probably not as important an issue for our entire organization.

I'm not entirely certain how to account for this discrepancy. I have tried to add in the number of respondents as an additional factor to the weighting but don't feel I've done this correctly (as depending on the way I massage the data, I get different priorities rising to the top).

Attached is a workbook with the actual data from the survey, but without the identifiers (i.e. I've changed the real categories to Strength 1 through X, Weakness 1 through X etc.)

Does anyone have an insight on how I might account for the number of respondents as well as the weighted average of their rankings? (I should point out that while everyone should have rated 7 things in each category, not everyone so far has. This is one of the major complications).

Tremendous thanks,

-LM

[Ace_XL]

Use SUMPRODUCT, basically don't use a weighted average, but a weighted SUM. This takes care of the issue where a strength has zero rating, which is lower than being ranked 7th

For table 1 in row 5 use

=SUMPRODUCT($B$4:$H$4,$B5:$H5)
or simply
=I5*J5

Copy down

[liquidmettle]

Hi Ace,

Thank you for the tremendously fast reply!

Would you consider the weighted sum to be an accurate representation then? (I had arrived at those values another way when I was tinkering before but the sumproduct formula is waaaaay easier than what I did so cheers for that!)

Its been a while since I've performed proper statistical muckings about.