-2

This one has been giving us a fit around the office here, so I'm reaching out to see if we can get some assistance. We recently finished collecting survey responses wherein the respondents ranked a series of "strategies" amongst themselves. What we are now trying to do is give each of those rankings a value in Excel so that we can determine the average rank of each "strategy".

Person 1
---------------------------------
Develop Sites
Advance Entrepreneurialism
Assist Small Businesses
Champion Skilled Labor
Leverage Local Talent
Connect With Tech

Person 2
---------------------------------
Advance Entrepreneurialism
Leverage Local Talent
Assist Small Businesses
Connect With Tech
Champion Skilled Labor
Develop Sites

That is an example of one of 7 responses for each respondent, some with more or less "strategies" to rank.

So far we have tried rooting around with a number of functions we are familiar with but have had no success. Any help would be greatly appreciated.

Seth K
  • 1

1 Answers1

0

Why not do something like this? Each person ranks from 1 - 6, and the response with the smallest "avg" is ranked the highest. Simply sort the avg from lowest to highest.

Survey          P 1     P 2     P 3     P 4     Avg
Develop Sites    1      5       4       2       3
Advance Entre    3      1       2       3       2.25
Assist Sm Bus    4      2       5       5       4
Leverage L Ta    2      4       6       4       4
Champion S La    5      3       1       1       2.5
Connect W Tec    6      6       3       6       5.25

If there are more than 6 possible responses, but respondents can only rank their top 6 - I would just give the "empty" responses a value of 7.

MarioS
  • 262
  • 1
  • 3
  • 12
  • This was an option we thought about; but it would require us to manually input the rank value for each ranking question, for a total of 400+ questions (7 ranking questions & 59 responses). Not saying it's not doable - but we were hoping for something a bit more automated. – Seth K May 29 '18 at 15:07
  • VBA would be required to automate this. But writing the code efficiently (without having the actual spreadsheet in front of us), would take more time and effort from your end (to answer our questions) than the time it would take for you to input the values manually. – MarioS May 29 '18 at 16:10
  • Unfortunately, the problem is how the data was stored. Going forward you can use the format/table that I have shown to efficiently rank the responses. – MarioS May 29 '18 at 16:11
  • Kinda figured that might be the case. Thanks for your help though. – Seth K May 29 '18 at 16:42