Finding Kth largest value in a column with duplicates

  • Thread starter Thread starter Serge80
  • Start date Start date
S

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
 
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
Click to expand...

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, Sergey,

Also:
=LARGE(IF(FREQUENCY(data,data)>0,data),K)

If Sergey's data (one column) contains blanks, he might try the following Array
formula:
=LARGE(IF(MMULT((data=TRANSPOSE(data))*(ROW(data)>=
TRANSPOSE(ROW(data))),ROW(data)^0)=1,data),K)

Regards,

Daniel M.
 
Hi All

One more option:

This array formula (to be entered with <Shift><Ctrl><Enter>)

=LARGE(IF(FREQUENCY(IF(Data="",FALSE,Data),
IF(Data="",0,Data))>0,Data),K)

also covers blanks in the column.

Notice the peculiarity (which may tell something
about, how the FREQUENCY() function works internally):

(Data="",FALSE,Data) and (Data="",0,Data)

If
(Data="",0,Data) and (Data="",0,Data)
is used instead, the formula will fail returning 0, if K is 1 larger
than possible.


If
(Data="",FALSE,Data) and (Data="",FALSE,Data)
or
(Data="",0,Data) and (Data="",FALSE,Data)
is used, the formula returns #N/A.

Discovered purely by accident :-)
 
Hi Leo,
=LARGE(IF(FREQUENCY(IF(Data="",FALSE,Data),IF(Data="",0,Data))>0,Data),K)
Click to expand...

or (to generate the False implicitly)
=LARGE(IF(FREQUENCY(IF(Data<>"",Data),IF(Data="",0,Data))>0,Data),K)
Click to expand...

Nice, but just beware of small problems with 0 and quasiblank.

For example, assuming:
K=3
data={12;-2;"";0;-3;3}
Here the third entry is a formula returning "" (so a _quasiblank_)

Should return 0 but returns -2

Interesting ideas anyway.

Regards,

Daniel M.
 
Daniel.M said:
Hi Leo,
=LARGE(IF(FREQUENCY(IF(Data="",FALSE,Data),IF(Data="",0,Data))>0,Data),K)

or (to generate the False implicitly)

Nice, but just beware of small problems with 0 and quasiblank.

For example, assuming:
K=3
data={12;-2;"";0;-3;3}
Here the third entry is a formula returning "" (so a _quasiblank_)

Should return 0 but returns -2

Interesting ideas anyway.

Regards,

Daniel M.
Click to expand...
Hi Daniel

Thanks for taking the time to check my "solution"!

The troublemaker is quite clearly the fact, that
having e.g. A1="" and A2=blank results in

=(A1="") and =(A2="") both returning TRUE.

That's the reason for the "strange" condition
IF(Data="","",Data)
The result is, that the array will get a "" no matter,
if the value from Data is "" or blank.

I have also made a single alteration to the formula, so
now it should work for blanks, quasiblanks and zero in
all combinations. At least I hope so :-)

=LARGE(IF(FREQUENCY(IF(Data<>"",Data,MAX(Data)+1),
IF(Data<>"",Data,MAX(Data)+1))>0,IF(Data="","",Data)),K)


I have used your formula

=LARGE(IF(MMULT((data=TRANSPOSE(data))*(ROW(data)>=
TRANSPOSE(ROW(data))),ROW(data)^0)=1,data),K)

on this dataset in A1:A11

20
50
=""
50
21
Blank
0
42
Blank
3
45

For K=7 your formula should return 0, but returns #NUM!

If I make a named formula (Insert > Name > Define)
for {12;-2;"";0;-3;3}, I can't get your formula to work.
It returns #VALUE!
 
Harlan Grove said:
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)
Click to expand...

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)
 
Leo,
=LARGE(IF(MMULT((data=TRANSPOSE(data))*(ROW(data)>=
TRANSPOSE(ROW(data))),ROW(data)^0)=1,data),K)

on this dataset in A1:A11

20
50
=""
50
21
Blank
0
42
Blank
3
45

For K=7 your formula should return 0, but returns #NUM!
Click to expand...

You're right.
There's some problem comparing 0 with blanks (but not quasiblanks!): it returns
True instead of False.
I think adding another test, ISNUMBER() for instance, would work but I'll test
that a little bit more.
If I make a named formula (Insert > Name > Define)
for {12;-2;"";0;-3;3}, I can't get your formula to work.
It returns #VALUE!
Click to expand...

Yes that's expected since the formula uses ROW() function and that can't make
sense for a name not referring to a range. I would call that a limitation ;-)
=LARGE(IF(FREQUENCY(IF(Data<>"",Data,MAX(Data)+1),
IF(Data<>"",Data,MAX(Data)+1))>0,IF(Data="","",Data)),K)
Click to expand...

Haven't tried your latest formula.
I'll do so when I get a little more time.

Have a nice day,

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

Sergey
 
Hi Leo,
I have also made a single alteration to the formula, so
now it should work for blanks, quasiblanks and zero in
all combinations. At least I hope so :-)

=LARGE(IF(FREQUENCY(IF(Data<>"",Data,MAX(Data)+1),
IF(Data<>"",Data,MAX(Data)+1))>0,IF(Data="","",Data)),K)
Click to expand...

Works very well for me. Thanks.

Daniel M.
 
Back
Top