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

[Guest]

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.

 

[Frank Kabel]

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}))

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

 

[Frank Kabel]

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

 

[Ron Rosenfeld]

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

 

[Aladin Akyurek]

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)),"")

 

[Biff]

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

 

[Ron Rosenfeld]

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

 

[Aladin Akyurek]

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

http://www.mrexcel.com/board2/viewtopic.php?t=48379&highlight=