How to find the most frequent number?

[nikonlubber2003]

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!

 

[Aladin Akyurek]

=MODE(Range)

 

[nikonlubber2003]

Thank you, that worked great. Do you know how to find the most common 4 numbers?

 

[Aladin Akyurek]

Thank you, that worked great. Do you know how to find the most common 4 numbers?

What is the range?

 

[nikonlubber2003]

Range could be A1 to I20.

 

[Aladin Akyurek]

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
	A	B	C	D	E	F	G	H	I
1	X1	X2	X3	X4	X5	X6	X7	X8	X9
2	35	28	23	35	24	31	32	25	26
3	21	28	26	32	26	23	23	34	32
4	20	31	34	22	34	32	21	23	26
5	27	28	35	21	21	24	28	25	35
6	28	20	28	29	23	24	29	20	32
7	26	22	31	23	35	35	29	20	26
8	21	34	33	23	20	27	35	34	30
9	33	26	24	33	30	34	26	20	33
10	23	25	30	34	24	20	29	30	32
11	26	30	34	23	30	32	30	24	28
12	31	34	23	32	25	21	33	35	22
13	27	23	28	20	30	35	27	35	28
14	22	25	24	22	25	31	31	22	31
15	21	23	26	21	34	34	21	27	20
16	23	27	35	22	21	28	25	22	27
17	21	21	21	28	21	32	26	20	34
18	33	28	35	35	20	21	29	30	20
19	22	21	29	24	27	34	25	23	35
20	25	31	22	23	24	35	29	20	27
21	28	20	28	24	31	21	31	32	27
Sheet1

See the next post for processing.

 

[Aladin Akyurek]

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
	J	K	L	M	N	O
1						4
2						1
3	Min	20	Distinct	Freq	Rank	ResultList
4	Max	35	20	14	4	21
5			21	17	1	23
6			22	10	8	35
7			23	15	2	20
8			24	10	9	28
9			25	9	13
10			26	11	7
11			27	10	10
12			28	14	5
13			29	7	15
14			30	9	14
15			31	10	11
16			32	10	12
17			33	6	16
18			34	13	6
19			35	15	3
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]

Range could be A1 to I20.

Alternative approach by means of array formulas...

Book11
	A	B	C	D	E	F	G	H	I	J	K	L
1	X1	X2	X3	X4	X5	X6	X7	X8	X9		4
2	35	28	23	35	24	31	32	25	26		Mode	Freq
3	21	28	26	32	26	23	23	34	32		21	17
4	20	31	34	22	34	32	21	23	26		35	15
5	27	28	35	21	21	24	28	25	35		23	15
6	28	20	28	29	23	24	29	20	32		28	14
7	26	22	31	23	35	35	29	20	26		20	14
8	21	34	33	23	20	27	35	34	30		34	13
9	33	26	24	33	30	34	26	20	33
10	23	25	30	34	24	20	29	30	32
11	26	30	34	23	30	32	30	24	28
12	31	34	23	32	25	21	33	35	22
13	27	23	28	20	30	35	27	35	28
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]

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.