Results 1 to 8 of 8
Thread: Data Analysis (Excel 2002)

20041212, 03:49 #1
 Join Date
 May 2004
 Location
 Australia
 Posts
 17
 Thanks
 0
 Thanked 0 Times in 0 Posts
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 (110) using the data provided?
Any help would be greatly appreciated!

20041212, 05:13 #2
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
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)?
Legare Coleman

20041212, 06:34 #3
 Join Date
 May 2004
 Location
 Australia
 Posts
 17
 Thanks
 0
 Thanked 0 Times in 0 Posts
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?

20041212, 07:35 #4
 Join Date
 Jan 2001
 Location
 Ankeny, Iowa, USA
 Posts
 298
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20041212, 11:09 #5
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,264
 Thanks
 2
 Thanked 478 Times in 393 Posts
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 ...
CheersCheers,
Paul Edstein
[MS MVP  Word]

20041212, 12:02 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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 reversesum can be calculated using an ARRAY formula (confirm with ctrlshiftenter). Add this to a cell:
<pre>=SUM(IF(ISNUMBER(B2:R2),11B2: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),(1B2: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^(10B2: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

20041212, 21:26 #7
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.
Legare Coleman

20041213, 12:41 #8
 Join Date
 May 2004
 Location
 Australia
 Posts
 17
 Thanks
 0
 Thanked 0 Times in 0 Posts
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!