lookup for top 10 values

See if this helps.


Input this formula in cell D2 and copy it down as far you needed:

=LARGE(B$2:B$8,ROW()-1)

Input this formula in cell E2 and copy it down as far you needed:

=INDEX(A$2:A$398,SMALL(IF(B$2:B$398=D2,ROW(B$2:B$398)-ROW(D$2)+1),COUNTIF(D$2:D2,D2)))

That formula is an-array so it must be confirmed with:

Ctrl+Shift+Enter

These are arrays and need to be confirmed with CTRL+SHIFT+ENTER

{=LARGE($B$1:$B$19,{1;2;3;4;5;6;7;8;9;10})}

swendingo

What do you want to happen if there are equals, that might mean more than 10 people share the top 10 durations?

Have you cosidered Data|Filter|AutoFilter then on the Duration column drop-down choose 'Top 10'?

The system I often used as can be seen in my post in:

http://www.mrexcel.com/board2/viewtopic.php?t=69970

is troublesome when the number of the data points/records reaches 5000, noticed in fairwinds's post:

http://www.mrexcel.com/board2/viewtopic.php?t=163028

Modifying for control vane's suggestion, we might have an improved Top N system:

E1: 3

which is the desired size of the Top N list.

E2:

=COUNTIF(B4:B10,">="&LARGE(B4:B10,E1))

which determines the actual size, dictated by data which might contain ties of the Nth value.

E4, copied down:

=IF(ROWS(E$4:E4)<=$E$2,LARGE($B$4:$B$10,ROWS(E$4:E4)),"")

which lists the actual Top N scores. Note that this list needs to be generated.

F4:

=IF(N(E4),INDEX(A$4:A$10,SMALL(IF(B$4:B$10=E4,ROW(B$4:B$10)-ROW(B$4)+1),COUNTIF(E$4:E4,E4))),"")

which must be confirmed with control+shift+enter (not just with enter) and copied down.

Thanks guys
I am customizing to suit I will let you know how it goes.

as far as the duplicates the data isn't insanely sensitive so if the there is a 3 way tie for 10th place it doesn't matter with I use.

Thanks guys
I am customizing to suit I will let you know how it goes.

as far as the duplicates the data isn't insanely sensitive so if the there is a 3 way tie for 10th place it doesn't matter with I use.

Click to expand...

It's a matter of correctness. Also, I don't think people involved would like to be left out.

Can't get this to work...

Mr. Aladin posted this great method for creating a top 10 list. I am attempting to adopt this to a spreadsheet however I need excel to only add a name to the top ten if the value in another cell is equal to a text string.

Still a newbie so any help would be greatly appreciated,

Go Bucks..

Great Info - was just about to start working on method of gathering Top 5 numbers so will begin here.

Question: Is there a way to look at both Positive & Negative amounts and pull Top 5 Largest Numbers?

Example:

10 Numbers [ 25,000; 15,000; 12,000; 10,000; 8,000; 4,000; -6,000; -11,000; -17,000; -32,000 ]

Top 5: -32,000; 25,000; -17,000; 15,000; 12,000

Thanks

Excel?,

Looks like you just need to utilize the Absolute value function when specifying your range. Using Mr. Aladin's post as an example:


=IF(ROWS(E$4:E4)<=$E$2,LARGE(ABS($B$4:$B$10),ROWS(E$4:E4)),"")

Hope this works. / Steve