How to find the most frequent number?

nikonlubber2003

New Member
Joined
Feb 20, 2003
Messages
17
Hi,

I have lots of numbers in several rows and several columns in a spreadsheet and I need to find the numbers that come up most frequently. Help!
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
nikonlubber2003 said:
Range could be A1 to I20.

I assume the data consists of whole numbers.

I should make what follows dynamic... In order to spare your nerves, I'm omitting that aspect. Once you're thru all this, we can come back on that aspect...

Data
Book11
ABCDEFGHI
1X1X2X3X4X5X6X7X8X9
2352823352431322526
3212826322623233432
4203134223432212326
5272835212124282535
6282028292324292032
7262231233535292026
8213433232027353430
9332624333034262033
10232530342420293032
11263034233032302428
12313423322521333522
13272328203035273528
14222524222531312231
15212326213434212720
16232735222128252227
17212121282132262034
18332835352021293020
19222129242734252335
20253122232435292027
21282028243121313227
Sheet1


See the next post for processing.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

Aladin Akyurek said:
nikonlubber2003 said:
Range could be A1 to I20.

...

See the next post for processing.

Processing

As the exhibit shows, the processing area is next to data for easier exposition...
Book11
JKLMNO
14
21
3Min20DistinctFreqRankResultList
4Max352014421
52117123
62210835
72315220
82410928
925913 
1026117 
11271010 
1228145 
1329715
1430914
15311011
16321012
1733616
1834136
1935153
20   
Sheet1

Formulas...

K3:

=MIN(A2:I21)

K4:

=MAX(A2:I21)

L4, copied down:

=IF(ROW()-ROW(L$4)+1<=$K$4-$K$3+1,ROW()-ROW(L$4)+$K$3,"")

M4, copied down:

=IF(ISNUMBER(L4),COUNTIF($A$2:$I$21,L4),"")

N4, copied down:

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

O1: 4 [ meaning: 4 most frequent ]

O2:

=MAX(IF(INDEX(M4:M19,MATCH(O1,N4:N19,0))=M4:M19,N4:N19))-O1

which must be confirmed with control+shift+enter instead of the usual enter.

O4, copied down:

=IF(ROW()-ROW(O$4)+1<=$O$1+$O$2,INDEX($L$4:$L$19,MATCH(ROW()-ROW(O$4)+1,$N$4:$N$19,0)),"")
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
nikonlubber2003 said:
Range could be A1 to I20.

Alternative approach by means of array formulas...
Book11
ABCDEFGHIJKL
1X1X2X3X4X5X6X7X8X94
2352823352431322526ModeFreq
32128263226232334322117
42031342234322123263515
52728352121242825352315
62820282923242920322814
72622312335352920262014
82134332320273534303413
9332624333034262033
10232530342420293032
11263034233032302428
12313423322521333522
13272328203035273528
Sheet2


Formulas...

K1: 4 [ meaning: 4 most frequent ]

K3:

=MODE(A2:I21)

K4:

=MODE(IF(ISNUMBER(MATCH($A$2:$I$21,$K$3:K3,0)),FALSE,$A$2:$I$21))

which must be confirmed with control+shift+enter instead of the usual enter and copied down.

L3, copied down:

=COUNTIF($A$2:$I$21,K3)

Note that a decision rule can be added to stop the array formula to produce additional results after the MODE value of 20 in order to mimick the previously posted formula system.
 

nikonlubber2003

New Member
Joined
Feb 20, 2003
Messages
17
Thank you very much. You have helped me through a very difficult problem that none of my coworkers could solve. I'll put this information to work as soon as possible.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,653
Messages
5,597,377
Members
414,141
Latest member
Joey_T92

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top