finding most frequent number in a list

Anyone know a formula, macro, etc. that will take a certain list, and find the most frequent number that appears? How about the least frequent number that occurs?

I would also like to find the second most common, third most common, etc. I know I could just delete all occurences of the previously most common number, but would like to avoid this. Any advice much appreciated.

[giacomo]

MODE will give you the most frequently occuring number in a range. The following will give you the second most frequent number (assuming your data is in the range A1:A10):

=MODE(IF(A1:A10<>MODE(A1:A10),A1:A10,"a"))

this is an array formula so hit CTRL+SHIFT+ENTER

The other stuff you're asking for would probably require a macro. An easy solution however would be to add a new column to your spreadsheet and use the COUNTIF function to find out how often each number occurs and then do a sort based on those numbers.

[Mark W.]

Enter into cell B2...

=COUNTIF(A:A,A1)

...and copy down for values entered into column A.

[ This Message was edited by: Mark W. on 2002-03-04 11:36 ]

[Aladin Akyurek]

On 2002-03-04 09:00, Anonymous wrote:
Anyone know a formula, macro, etc. that will take a certain list, and find the most frequent number that appears? How about the least frequent number that occurs?

I would also like to find the second most common, third most common, etc. I know I could just delete all occurences of the previously most common number, but would like to avoid this. Any advice much appreciated.

Consider the following sample in A1:A6.

{2;5;2;4;4;3}

In B1 enter:

=COUNTIF($A$1:$A$6,A1)+COUNTIF($A$1:A1,A1)-1

In B2 enter:

=IF(ISNUMBER(MATCH(A2,$A$1:A1,0)),"",COUNTIF($A$1:$A$6,A2)+COUNTIF($A$1:A2,A2)-1)

Copy down the last formula to the last row of data in A.

In C1 enter:

=IF(ISNUMBER(B1),RANK(B1,$B$1:$B$6,1)+COUNTIF($B$1:B1,B1)-1,"")

Copy down this to the last row of data in A.

In D1 enter:

=INDEX($A$1:$A$6,MATCH(LARGE($C$1:$C$6,ROW()),$C$1:$C$6,0))

Note. ROW() gives 1 in D1, 2 in D2 etc.

Copy down this e.g. to the 3rd row of data, which will give you the first, second, and third most frequently occurring numbers.