Ron Rosenfeld wrote...
....
The following, somewhat cumbersome, single cell solution might work.
Unfortunately, I have not figured out how to just enter a single formula and
drag it down as required.
....

A single formula would be tricky if the topmost formula (the first
mode) could be in row 1. The simplest way to deal with that may be
found in Dominic's response.

As for a single cell formula that returned the n_th most frequently
occurring number in a multiple row, single column range, rng, which
contained numbers in every cell, try the array formula

=IF(n<=SUMPRODUCT(1/COUNTIF(rng,rng)),INDEX(rng,
MATCH(LARGE(FREQUENCY(rng,rng)-ROW($A$1:INDEX($A:$A,ROWS(rng)+1))/100000,n),
FREQUENCY(rng,rng)-ROW($A$1:INDEX($A:$A,ROWS(rng)+1))/100000,0)),"")

On 1 Feb 2006 13:11:19 -0800, "Harlan Grove" wrote:

Ron Rosenfeld wrote...
...
The following, somewhat cumbersome, single cell solution might work.
Unfortunately, I have not figured out how to just enter a single formula and
drag it down as required.
...

A single formula would be tricky if the topmost formula (the first
mode) could be in row 1. The simplest way to deal with that may be
found in Dominic's response.

As for a single cell formula that returned the n_th most frequently
occurring number in a multiple row, single column range, rng, which
contained numbers in every cell, try the array formula

=IF(n<=SUMPRODUCT(1/COUNTIF(rng,rng)),INDEX(rng,
MATCH(LARGE(FREQUENCY(rng,rng)-ROW($A$1:INDEX($A:$A,ROWS(rng)+1))/100000,n),
FREQUENCY(rng,rng)-ROW($A$1:INDEX($A:$A,ROWS(rng)+1))/100000,0)),"")

That's nice; it works; and now I have to figure out how!

Thanks.


--ron