Top 3 Formula Help (Excel 97)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

Thanks so much that worked perfectly!

Reply | Quote

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$5:D5,D5)-1

assigns a ranking per consideration.

D13: 3

indicates N of Top N you want.

D14:

=MAX(IF(INDEX(D5:D11,MATCH(D13,E5:E11,0))=D5:D11,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(ROW()-ROW($D$16)+1,$E$5:$E$11,0)),"")

creates the correct Top 3 list of considerations you are after.

Reply | Quote

Nice general approach!

Reply | Quote