Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Aug 2004
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Top 3 Formula Help (Excel 97)

    I need some more assistance with a survey results spreadsheet I am working on. Companies were polled and ranked their top 3 choices. I need a way to determine what the top 3 results were. I would like the results to appear something like this:

    #1 - Experience
    #2 - Service
    #3 - Relationship

    I figured out a way to count how many times each answer appears. But I don't know where to go from there. Any ideas would be greatly appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Top 3 Formula Help (Excel 97)

    You can use the RANK function to find the 3 highest scores; I used a modified version to construct unique ranks from Ranking Data In Lists.
    Next, you can combine this with MATCH and INDEX to find the corresponding choices. See attached.

  3. #3
    Star Lounger
    Join Date
    Aug 2004
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Top 3 Formula Help (Excel 97)

    Thanks so much that worked perfectly!

  4. #4
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Top 3 Formula Help (Excel 97)

    The problem you want to solve belongs to the class of Top N problems, where N = 1, 2,...

    What follows is a formula system I devised to tackle with such problems. The system results agree with the results one would obtain with a pivot table.

    The data in C4:AA4 consists of multiple items (considerations like Service, Knowledge, Experience) per cell obtained by means of surveys. (See the attachment for references.)

    Note that the data is a bit altered to better illustrate the point of the formula system.

    The problem: Create a Top 3 list of considerations, based on their occurrence frequencies.

    C5:C11 lists the 'conisderations' of interest.

    D5, copied down:

    =COUNTIF($C$4:$AA$4,"*"&C5&"*")

    calculates the frequency of occurrence per consideration.

    E5, copied down:

    =RANK(D5,$D$5:$D$11)+COUNTIF($D$55,D5)-1

    assigns a ranking per consideration.

    D13: 3

    indicates N of Top N you want.

    D14:

    =MAX(IF(INDEX(D511,MATCH(D13,E5:E11,0))=D511,E5:E11))-D13

    which must be confirmed with control+shift+enter, not just with enter.

    This "kernel" formula calculates the ties of the Nth value (frequency of occurrence).

    D16, copied down:

    =IF(ROW()-ROW($D$16)+1<=$D$13+$D$14,INDEX($C$5:$C$11,MATCH(R OW()-ROW($D$16)+1,$E$5:$E$11,0)),"")

    creates the correct Top 3 list of considerations you are after.
    Microsoft MVP - Excel

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Top 3 Formula Help (Excel 97)

    Nice general approach!

Posting Permissions

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