finding most frequent number in a list

[Guest]

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.

 

[Nanaia]

If you're putting this information on a separate sheet and the range of numbers being counted/checked changes from job to job so there isn't a reference list available, is there a way to get Excel to tell you what the most frequently occurring numbers are instead of developing a list of every number used first?

 

[Eric W]

Aladin's formulas (columns B:D) don't require a reference list. Here's a single array formula that also doesn't need a reference list:

	A	B	C	D	E	F
List

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"]#NUM![/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]#NUM![/TD]
[TD="align: right"][/TD]
[TD="align: right"]#N/A[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]{=MODE(IF(COUNTIF($F$1:$F1,$A$1:$A$6)=0,$A$1:$A$6*{1,1}))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[Nanaia]

It keeps giving me 0 for the result. I may have entered my reference column in the wrong location. There's only 1 column, CSV!S3:S201. It also contains blank cells which would need to be ignored. I am trying to find the top 5 most occurring numbers in the list.
I used MODE to find the most common and was going to simply list the 2nd, 3rd, etc below the mode formula.

 

[Eric W]

Use this version to ignore blank cells:

=MODE(IF(COUNTIF($F$1:$F1,CSV!$S$3:$S$201)=0,IF(ISNUMBER(CSV!$S$3:$S$201),CSV!$S$3:$S$201*{1,1})))

with Control+Shift+Enter. And the $F$1:$F1 must be the cell immediately above the cell you put this formula in.

 

[Nanaia]

Awesome! Thank you!

 

[Eric W]

Cool! Glad it works for you!


Incidentally, I was considering how to handle multiple modes. You could put a COUNTIF next to your output column to see how many there are, and two rows with the same count will show up next to each other. You could also use the MODE.MULT function to do something like this:

	A	B	C	D	E	F	G	H
List

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2:K2[/TH]
[TD="align: left"]{=IFERROR(SMALL(MODE.MULT(IF(COUNTIF($F$1:$K1,$A$1:$A$14)=0,IF(ISNUMBER($A$1:$A$14),$A$1:$A$14*{1,1}))),{1,2,3,4,5}),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

All the numbers that occur the same number of times (up to 5) will show up on the same row. Just letting my mind wander!