Mode() & Large()

[Guest]

Ok .. i know how to return the number used most in a range;

=MODE(A1:A15)

and i know how to find the K-th largest number;

=LARGE(A1:A15,k)

but what i can't figure out is how to find the 2nd or 3rd most used number.
basically i need to combine the 2 functions.

any help .. please, thanks in advance.

 

[Harlan Grove]

cheshire191 wrote...

Ok .. i know how to return the number used most in a range;

=MODE(A1:A15)

and i know how to find the K-th largest number;

=LARGE(A1:A15,k)

but what i can't figure out is how to find the 2nd or 3rd most used number.
basically i need to combine the 2 functions.

Forget MODE. One way to find the k_th most frequent number in the range
rng is

=INDEX(rng,MATCH(LARGE(FREQUENCY(rng,rng), k ),FREQUENCY(rng,rng),0))

 

[Aladin Akyurek]

See my post in:

See:


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