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

Assuming that A2:A10 contains your data, here are two options...

[Option 1]

B1:

=MODE(A2:A10)

B2, copied down:

=MODE(IF(COUNTIF(B$2:B2,A$2:A$10)=0,A$2:A$10))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

[Option 2]

This option will generate a Top N list (Top 1, Top 3, Top 5, etc.),
where you choose N, and which will take into consideration ties for Nth
place...

B2, copied down:

=IF(ISNA(MATCH(A2,A$1:A1,0)),COUNTIF(A2:A$10,A2)," ")

C2, copied down:

=IF(N(B2),RANK(B2,B$2:B$10)+COUNTIF(B$2:B2,B2)-1,"")

D1: enter 3, indicating you want a Top 3 list

*Enter the Top N list of interest

E1:

=MAX(IF(B2:B10=INDEX(B2:B10,MATCH(D1,C2:C10,0)),C2 :C10))-D1

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

F2, copied down:

=IF(ROWS(F$2:F2)<=$D$1+$E$1,INDEX(A$2:A$10,MATCH(R OWS(F$2:F2),$C$2:$C$10,
0)),"")

Hope this helps!

In article ,
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

If your numbers are in column A, with a heading in cell A1, then enter this formula in Cell B2, and
copy down to match column A:

=IF(COUNTIF($A$1:A2,A2)=1,COUNTIF(A:A,A2)+ROW()/100000,"")

Then in C2, enter this formula, and copy down for as many rows as modes you want:

=INDEX(A:A,MATCH(LARGE(B:B,ROW()-1),B:B,FALSE))


HTH,
Bernie
MS Excel MVP


"Laffin" wrote in message
...
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

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

"Ron Rosenfeld" wrote...
....
=INDEX(rng,MATCH(LARGE(UNIQUEVALUES(COUNTIF(rng,r ng)),
H2),COUNTIF(rng,rng),0))
....

What about multiple modes? If A1:A6 contained {1;2;1;3;2;3}, MODE(A1:A6)
would return 1, the first mode, but 2 and 3 would also be modes. Your
UNIQUEVALUES call would return {2;"";"";"";"";"";""}.

On Tue, 31 Jan 2006 08:17:36 -0800, "Harlan Grove" wrote:

"Ron Rosenfeld" wrote...
...
=INDEX(rng,MATCH(LARGE(UNIQUEVALUES(COUNTIF(rng, rng)),
H2),COUNTIF(rng,rng),0))
...

What about multiple modes? If A1:A6 contained {1;2;1;3;2;3}, MODE(A1:A6)
would return 1, the first mode, but 2 and 3 would also be modes. Your
UNIQUEVALUES call would return {2;"";"";"";"";"";""}.



Good point. It would skip some numbers.


--ron

On Tue, 31 Jan 2006 10:21:11 -0500, Ron Rosenfeld
wrote:

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

Harlan pointed the error of my method in that it does not account for multiple
entries with the same frequency.

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.

But, again using the same add-in as above (using the VSORT function)

(all of the formulas are **array** entered formulas)

Most frequent:
D1: =VSORT(rng,COUNTIF(rng,rng))

2nd most
D2:
=INDEX(VSORT((rng<D1)*rng,COUNTIF(rng,rng)),
MATCH(TRUE,0<VSORT((rng<D1)*rng,COUNTIF(rng,rng)) ,0))

3rd most
D3:
=INDEX(VSORT((rng<D1)*(rng<D2)*rng,
COUNTIF(rng,rng)),MATCH(TRUE,0<VSORT((
rng<D1)*(rng<D2)*rng,COUNTIF(rng,rng)),0))

4th most
D4:
=INDEX(VSORT((rng<D1)*(rng<D2)*(rng<D3)*rng,
COUNTIF(rng,rng)),MATCH(TRUE,0<VSORT((
rng<D1)*(rng<D2)*(rng<D3)*rng,COUNTIF(rng,rng)) ,0))

Note the changes in this segment which occurs twice in each formula:

(rng<D1)*rng

(rng<D1)*(rng<D2)*rng

(rng<D1)*(rng<D2)*(rng<D3)*rng

It seems there should be some method of automating this change, but I have not
stumbled upon it as yet.



--ron

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

Option 1 causes a circular reference error (putting references to B2 in cell
B2); I think you meant B1 instead of B2 in the formula.

Then again, I might be wrong altogether.

"Domenic" wrote:

Assuming that A2:A10 contains your data, here are two options...

[Option 1]

B1:

=MODE(A2:A10)

B2, copied down:

=MODE(IF(COUNTIF(B$2:B2,A$2:A$10)=0,A$2:A$10))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

[Option 2]

This option will generate a Top N list (Top 1, Top 3, Top 5, etc.),
where you choose N, and which will take into consideration ties for Nth
place...

B2, copied down:

=IF(ISNA(MATCH(A2,A$1:A1,0)),COUNTIF(A2:A$10,A2)," ")

C2, copied down:

=IF(N(B2),RANK(B2,B$2:B$10)+COUNTIF(B$2:B2,B2)-1,"")

D1: enter 3, indicating you want a Top 3 list

*Enter the Top N list of interest

E1:

=MAX(IF(B2:B10=INDEX(B2:B10,MATCH(D1,C2:C10,0)),C2 :C10))-D1

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

F2, copied down:

=IF(ROWS(F$2:F2)<=$D$1+$E$1,INDEX(A$2:A$10,MATCH(R OWS(F$2:F2),$C$2:$C$10,
0)),"")

Hope this helps!

In article ,
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

Thanks for catching that error! The first formula should be entered in
B2, and the second one in B3.

Thanks again! Much appreciated!

In article ,
Rothman wrote:

Option 1 causes a circular reference error (putting references to B2 in cell
B2); I think you meant B1 instead of B2 in the formula.

Then again, I might be wrong altogether.

"Domenic" wrote:

Assuming that A2:A10 contains your data, here are two options...

[Option 1]

B1:

=MODE(A2:A10)

B2, copied down:

=MODE(IF(COUNTIF(B$2:B2,A$2:A$10)=0,A$2:A$10))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

[Option 2]

This option will generate a Top N list (Top 1, Top 3, Top 5, etc.),
where you choose N, and which will take into consideration ties for Nth
place...

B2, copied down:

=IF(ISNA(MATCH(A2,A$1:A1,0)),COUNTIF(A2:A$10,A2)," ")

C2, copied down:

=IF(N(B2),RANK(B2,B$2:B$10)+COUNTIF(B$2:B2,B2)-1,"")

D1: enter 3, indicating you want a Top 3 list

*Enter the Top N list of interest

E1:

=MAX(IF(B2:B10=INDEX(B2:B10,MATCH(D1,C2:C10,0)),C2 :C10))-D1

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

F2, copied down:

=IF(ROWS(F$2:F2)<=$D$1+$E$1,INDEX(A$2:A$10,MATCH(R OWS(F$2:F2),$C$2:$C$10,
0)),"")

Hope this helps!

In article ,
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