On Tue, 31 Jan 2006 06:08:41 -0800, Laffin
wrote:

I have a large data set. Within the column I have determined the mode. How
do I find the second most common number, third most common, etc.?

240
240
240
240
240
240
240
240
288
288
288
300

I'm sure someone will come up with a native worksheet function method.
However, I find the functions in Longre's free morefunc.xll add-in (available
from http://xcell05.free.fr/ quite useful, and this is no exception.

The following **array** formula, which makes use of the morefunc UNIQUEVALUES
function, should do what you require.

To enter an **array** formula, hold down <ctrl<shift while hitting <enter.
XL will place braces {...} around the formula:

=INDEX(rng,MATCH(LARGE(UNIQUEVALUES(
COUNTIF(rng,rng)),H2),COUNTIF(rng,rng),0))

H2 in the above contains a number which represents the frequency:

1: most common
2: 2nd most common
etc.


--ron