# Thread: Top 3 Formula Help (Excel 97)

1. ## 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. ## 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. ## Re: Top 3 Formula Help (Excel 97)

Thanks so much that worked perfectly!

4. ## 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.

5. ## 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
•