Find the three largest values and return the corresponding cell

[Losguapos1]

So here is my dilemma, I have raw scores for an eleven section test. I want to be able to list the strengths (top 3 scores) and weaknesses of the students (Bottom 3 scores). The section number corresponds to a section title listed on the sample page.

Is there any way to auto populate these fields so i don't have to look the through the data and determine the top and bottom three.

I have attached a sample with the desired results

Any help would be greatly appreciated.

Los

[Losguapos1]

this has really got me stumped.

I hope my explanation was clear, if not i can clarify.

Thanks in advance

Los

[6StringJazzer]

The strategy here is to use the LARGE and SMALL functions to find the top and bottom three scores, then take the result and use it look up which sections those occurred in. See attached example for Student 2.

However, this just shows the section number. You need to provide a list of section titles to be able to return a section title.

[Losguapos1]

thanks you for the reply, there is a section list starting in row 21 or 20 with section number and section title.

How would i add that in to this formula to achieve my desired results


TIA

Los

[Losguapos1]

And also i don't want the strengths or weaknesses to repeat if they have already popped up. currently section 5 occurs twice.

[CAntosh]

Ties are the problem. You can add a helper row, which would be obtrusive, to say the least, or try the following:

Change your formula in AT4 (currently =E4) to =E4+0.001*COLUMN(), then change AU4 (currently =I4) to =I4+0.001*COLUMN(), etc. for AT4 through BD4. This will keep your numbers looking the same in the workbook, but it will ever-so-slightly break any ties that occur and give you a proper ranking. Once that's done, enter the following formulas where indicated:

Please Login or Register  to view this content.

Fill down for each and you should be good to go...?

[Losguapos1]

THANK YOU!!! you rock