finding most frequent number in a list

Guest · Mar 4, 2002

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 · Mar 4, 2002

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. · Mar 4, 2002

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 · Mar 4, 2002

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 · Mar 29, 2018

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 · Mar 29, 2018

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 · Mar 29, 2018

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 · Mar 29, 2018

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 · Mar 29, 2018

Awesome! Thank you!

Eric W · Mar 29, 2018

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!

finding most frequent number in a list

Guest

Guest

Excel Facts

giacomo

Well-known Member

Mark W.

MrExcel MVP

Aladin Akyurek

MrExcel MVP

Nanaia

Active Member

Eric W

MrExcel MVP

Nanaia

Active Member

Eric W

MrExcel MVP

Nanaia

Active Member

Eric W

MrExcel MVP

Similar threads

Forum statistics

Share this page

finding most frequent number in a list

Guest

Guest

Excel Facts

Well-known Member

MrExcel MVP

MrExcel MVP

Active Member

MrExcel MVP

Active Member

MrExcel MVP

Active Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock