Finding Kth largest value in a column with duplicates

[Serge80]

I tried using =Index(Match(Large())), but I have list containing
duplicate values, so I get back duplicate values. Ex: {3,5,5,2} will
return 5,5,3,2, but I need only 5,3,2.. duh! Large(

Some examples suggested to increment list values by small number, but
I don't understand how that works:
{5+0.0001, 5+0.0002, 3+0.0004, 2+0.0005} will still return 5,5,3,2

Please help rank list with duplicate values.

Sergey

 

[Frank Kabel]

Hi
see
http://www.cpearson.com/excel/rank.htm
('Unique ranks')

 

[Aladin Akyurek]

Might also be of interest to you:

http://216.92.17.166/board2/viewtopic.php?t=69970

 

[Harlan Grove]

I tried using =Index(Match(Large())), but I have list containing
duplicate values, so I get back duplicate values. Ex: {3,5,5,2} will
return 5,5,3,2, but I need only 5,3,2.. duh! Large(

Some examples suggested to increment list values by small number, but
I don't understand how that works:
{5+0.0001, 5+0.0002, 3+0.0004, 2+0.0005} will still return 5,5,3,2

If you want to rank the list, follow the link Frank Kabel gave you. If you want
to find the Kth largest entry in a single column, multiple row range or
array(which I'll name Data) ignoring duplicates, try the array formula

=LARGE(IF(MATCH(Data,Data,0)=ROW(Data)-CELL("Row",Data)+1,Data),K)

 

[Leo Heuser]

If you want to rank the list, follow the link Frank Kabel gave you. If you want
to find the Kth largest entry in a single column, multiple row range or
array(which I'll name Data) ignoring duplicates, try the array formula

=LARGE(IF(MATCH(Data,Data,0)=ROW(Data)-CELL("Row",Data)+1,Data),K)

Hi Harlan

Can you get your formula to work on the named formula (Insert > Name >
Define)
for {12;-2;"";0;-3;3}? I get #VALUE! (Excel 2000)

 

[Serge80]

Thanks to everyone for their fast and correct responses. Daniel,
Frequency function works very well for my case and formula looks very
elegant.

Sergey