How do I get the 2nd Mode in a range of cells

I want to attain the 2nd, 3rd, 4th, etc. most frequent
number in a range of cells. Using the Mode function I can
receive the most frequent but how do I get the 2nd, 3rd,
4th,etc. from the same range.
Thanks.

Hi
assuming your list of numbers is in A1:A10 try the following formulas:
B1: = MODE($A$1:$A$10)
B2: (array entered with CTRL+SHIFT+ENTER) =
MODE(IF(COUNTIF($B$1:B1,$A$1:$A$10)=0,$A$1:$A$10+{ 0,0}))


--
Regards
Frank Kabel
Frankfurt, Germany


wrote:
I want to attain the 2nd, 3rd, 4th, etc. most frequent
number in a range of cells. Using the Mode function I can
receive the most frequent but how do I get the 2nd, 3rd,
4th,etc. from the same range.
Thanks.

On Sun, 3 Oct 2004 03:29:42 +0200, "Frank Kabel"
wrote:

Hi
assuming your list of numbers is in A1:A10 try the following formulas:
B1: = MODE($A$1:$A$10)
B2: (array entered with CTRL+SHIFT+ENTER) =
MODE(IF(COUNTIF($B$1:B1,$A$1:$A$10)=0,$A$1:$A$10+ {0,0}))

Frank,

I have not figured out a formulaic way of doing this, but I tried your method
on a random list of 150 numbers, and it misses quite a few when multiple
numbers have the same frequency.

Perhaps I am doing something wrong?

--ron
--ron

On Sat, 2 Oct 2004 10:26:35 -0700, wrote:

I want to attain the 2nd, 3rd, 4th, etc. most frequent
number in a range of cells. Using the Mode function I can
receive the most frequent but how do I get the 2nd, 3rd,
4th,etc. from the same range.
Thanks.


One method to do this:

1. Sort the range descending.
2. Data/Subtotals Use Function: Count
3. Click on the (2) button to collapse seeing only the Counts.
4. Sort the visible column again, descending.

Another method would be to use a Pivot Table.

Drag the Data to the Rows and Data field. Right Click the data field to select
Count as the function to use; then sort descending in the Total Column


--ron

Hi Ron
formulas work for me (also with numbers which have the same frequency).
If you like send me your file and I'll just check this (now getting
curious of course).

email: Frank[dot]kabel[at]freenet[dot]de



--
Regards
Frank Kabel
Frankfurt, Germany


Ron Rosenfeld wrote:
On Sun, 3 Oct 2004 03:29:42 +0200, "Frank Kabel"
wrote:

Hi
assuming your list of numbers is in A1:A10 try the following
formulas: B1: = MODE($A$1:$A$10)
B2: (array entered with CTRL+SHIFT+ENTER) =
MODE(IF(COUNTIF($B$1:B1,$A$1:$A$10)=0,$A$1:$A$10+{ 0,0}))

Frank,

I have not figured out a formulaic way of doing this, but I tried
your method on a random list of 150 numbers, and it misses quite a
few when multiple numbers have the same frequency.

Perhaps I am doing something wrong?

--ron
--ron

On Sun, 3 Oct 2004 11:08:58 +0200, "Frank Kabel"
wrote:

Hi Ron
formulas work for me (also with numbers which have the same frequency).
If you like send me your file and I'll just check this (now getting
curious of course).

Well, I guess I was doing something wrong last night, as today I cannot
reproduce the error.

I was probably adjusting your formula incorrectly to the larger range.

But it seems to work fine now. I must have been tired, last night, or
something. Sorry.


--ron

Let A3:A19 house:

{"Num";1;2;4;4;4;3;6;6;6;5;7;7;8;8;1;7}

where Num is just a label in A3.

In B3 enter: Count [ which is just a label ]

In B4 enter & copy down:

=IF(ISNUMBER(A4)*ISNA(MATCH(A4,$A$3:A3,0)),COUNTIF ($A$4:$A$19,A4),"")

In C3 enter: Rank [ which is just a label ]

In C4 enter & copy down:

=IF(N(B4),RANK(B4,$B$4:$B$19)+COUNTIF($B$4:B4,B4)-1,"")

In D1 enter, for example: 4 [ which means 4 most frequent numbers ]

In D2 enter:

=MAX(IF(INDEX(B4:B19,MATCH(D1,C4:C19,0))=B4:B19,C4 :C19))-D1

which you need to confirm with control+shift+enter instead of just with
enter.

In D3 enter: Mode [ which is just a label ]

In E3 enter: Freq [ which is just a label ]

In D4 enter and copy across to E4 then down:

=IF(ROW()-ROW(D$4)+1=$D$1+$D$2,INDEX(A$4:A$19,MATCH(ROW()-ROW(D$4)+1,$C$4:$C$19,0)),"")

wrote in message
...
I want to attain the 2nd, 3rd, 4th, etc. most frequent
number in a range of cells. Using the Mode function I can
receive the most frequent but how do I get the 2nd, 3rd,
4th,etc. from the same range.
Thanks.

Hi Ron and Frank!

How strange is this:

I also was getting incorrect results last night when I
tried Frank's formula. But now, it's working just fine!

Good one Frank.

Biff

-----Original Message-----
On Sun, 3 Oct 2004 11:08:58 +0200, "Frank Kabel"

wrote:

Hi Ron
formulas work for me (also with numbers which have the
same frequency).
If you like send me your file and I'll just check this
(now getting
curious of course).

Well, I guess I was doing something wrong last night, as
today I cannot
reproduce the error.

I was probably adjusting your formula incorrectly to the
larger range.

But it seems to work fine now. I must have been tired,
last night, or
something. Sorry.


--ron
.

On Sun, 3 Oct 2004 17:27:25 -0700, "Biff" wrote:

Hi Ron and Frank!

How strange is this:

I also was getting incorrect results last night when I
tried Frank's formula. But now, it's working just fine!

Good one Frank.

Biff

That is very strange. Must have been the phase of the moon, or something. :~)


--ron

For another approach, using MODE() and MATCH(), see my contrib in:

http://www.mrexcel.com/board2/viewto...379&highlight=

wrote in message
...
I want to attain the 2nd, 3rd, 4th, etc. most frequent
number in a range of cells. Using the Mode function I can
receive the most frequent but how do I get the 2nd, 3rd,
4th,etc. from the same range.
Thanks.