Results 1 to 8 of 8
  1. #1
    New Lounger
    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 (1-10) using the data provided?

    Any help would be greatly appreciated!

  2. #2
    Uranium Lounger
    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

  3. #3
    New Lounger
    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?

  4. #4
    3 Star Lounger
    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.

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 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 ...

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #6
    WS Lounge VIP sdckapr's Avatar
    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 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. #7
    Uranium Lounger
    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

  8. #8
    New Lounger
    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!

Posting Permissions

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