1. Data Analysis (Excel 2002)

Hello, maybe someone can help me with this problem. I have received some data and am attemting to make sense of it. The data is the result of a survey in which people were given 26 issues and asked to identify their top ten in order, 1 being the most important whilst 10 was the least important. In doing this, obviously 16 issues did not rate at all.

In the attached file I have laid out the data in a format which seems sensible to me. Here' what I'm struggling with:
How do I determine the most important issues (1-10) using the data provided?

Any help would be greatly appreciated!

2. Re: Data Analysis (Excel 2002)

What is your definition of "the most important issues"? Is the most important issue the one that has the most "1" responses? The one with the lowest average response? The one with the most responses (ie important to the most people)?

3. Re: Data Analysis (Excel 2002)

Hi Legare , thanks for taking the time to reply.
If we look at some sequences, then the following would apply:
On its own 1 is more important than any other digit.
10,10 is more important than 1.
10,10,10,1 is more important than 1,1 however
1,1,1 is more important than 10,10,10.

These are only my thoughts. Do you know if there is a 'convention' for these sort of things? At the end of the day, I think numerous low rankings are more important than a lesser number of 1's, unless of course there are '3' 10's and '2' 1's. Bit hard to say really. Your thoughts?

4. Re: Data Analysis (Excel 2002)

In my company, every year we vote on what we think the "priority issues" for our department should be for the next year. We brainstorm a list of things that need to be looked at, combine similar items, and then each person ranks these issues to vote for the top 5 issues to focus on. I would guess that this is very much like what you have done here.

To get the top issues, we assign each vote a point value, which I think will tell you what you're wanting to know. In your case, you would assign a rank of 1 ten points, a ranking of 2 nine points, and so on until you get to a ranking of 10 which would get one point. Then you simply add up all of the points for each item and that gives you your top ten issues. In your example, the first issue (Provide and/or Coordinate Training for Dog Management...) would score 103, the second issue (Provide and/or Coordinate Training for Customer Service...) would score 58 points, and so on.

5. Re: Data Analysis (Excel 2002)

Hi,

There are various ways to approach this, depending on what you're trying to achieve. For example, you might rate the preferences according to the number of 1s received for each topic. Alternatively, each prefernce might be weighted in some way. The latter seems more consistent with your data collection method, but you'll then need to decide whether a '2' is worth, say, 90% or 50% of a 1, and so on. Once you've done that, a series of COUNTIFs for each row could be used to determine the most poular topics. For example:
=COUNTIF(B2:R2,1)+COUNTIF(B2:R2,2)*0.9+COUNTIF(B2: R2,3)*0.8+COUNTIF(B2:R2,4)*0.7+COUNTIF(B2:R2,5)*0. 6+COUNTIF(B2:R2,6)*0.5+COUNTIF(B2:R2,7)*0.4+COUNTI F(B2:R2,8)*0.3+COUNTIF(B2:R2,9)*0.2+COUNTIF(B2:R2, 10)*0.1
in W2 and copied down as far as needed.
The other thing you'll need is some form of data validation:
response 6 has no 4th preference;
response 8 has no 2nd preference but two 4th preferences; and
response 12 has 11 preferences, including two 1st preferences, no 10th preference and a 13th preference ...

Cheers

6. Re: Data Analysis (Excel 2002)

You have already gooten several responses.

I would use (like BAN suggested) the "polyvote". You could "reverse sum" (give the 1 a value of 10, 2 a value of 9 etc) or even give them some "ranking" (similar to what macropod suggested) to do it or do some weighting of the numbers.

I could be done by just sorting your data on the mean (or even the median) but this may be misleading, since a small number of people may influence this (1 person choosing something as a "1" and it not being chosen by anyone else will rate higher 16 rating something a "1" and the other rating it a "2").

The reverse-sum can be calculated using an ARRAY formula (confirm with ctrl-shift-enter). Add this to a cell:
<pre>=SUM(IF(ISNUMBER(B2:R2),11-B2:R2))</pre>

Then copy it down the rows. You can sort by this value for the ranking.

You could also use an ARRAY formula to get to macropod's system:

<pre>=SUM(IF(ISNUMBER(B2:R2),(1-B2:R2)/10+1))</pre>

Of course, you could determine some other "factor" based on the number to weight them differently. If you wanted to give a score of 10 a value of 1, a score of 9 a value of 2, 8 a value of 4, 7 a value of 8 (doubling each one) until you give a score of 1 a value of 512, you could do it with this ARRAY formula:

<pre>=SUM(IF(ISNUMBER(B2:R2),2^(10-B2:R2)))</pre>

Personally, I don't think there should be big differences in the way you do it, and I would keep it simple. The reverse sum is easily explained and calculated and understandable so I tend to stick with that.
Steve

7. Re: Data Analysis (Excel 2002)

I think that you have to define what makes an issue important. What I think makes it important might be completely different than what you think, and they are your issues, not mine.

8. Re: Data Analysis (Excel 2002)

Thanks everybody for your helpful suggestions. You've really helped me crystilise my thoughts on how to approach this. I'll play around with the data and see what I come up with.

Have a great day!

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•