List the most common value in a range

[PatPat]

Hello

Can someone please guide me as to what function to use so that I could extract the most common value in a range?

For example if the range I have contains the following

a
s
a
a
h
t
y
u
a
a


I would want the value a to be displayed.

Thanks in anticipation

p

 

[NBVC]

If the list consists of one-letter strings as per you sample then possibly this will work:

=CHAR(MODE(CODE(B1:B10)))

else if the strings are multi-letter (e.g names), then try

=INDEX(B1:B10,MATCH(MAX(COUNTIF(B1:B10,B1:B10)),COUNTIF(B1:B10,B1:B10),0))

Where B1:B10 contains the range to look in.

The latter formula must be confirmed with Ctrl+Shift+Enter not just Enter. You should see {} brackets around the formula if entered correctly.

 

[Aladin Akyurek]

Hello

Can someone please guide me as to what function to use so that I could extract the most common value in a range?

For example if the range I have contains the following

a
s
a
a
h
t
y
u
a
a


I would want the value a to be displayed.

Thanks in anticipation

p

MostCommonValue.xls
	A	B	C	D	E	F	G	H
1		2			5
2	Value	Freq			Most Common Value(s)		Count of Value
3	a	5			a		Value	Total
4	s	1			u		a	5
5	a						u	5
6	a
7	h	2
8	t	2
9	y	2
10	u	5
11	a
12	a
13	u
14	t
15	u
16	u
17	y
18	u
19	h
Sheet1

B1:

=COUNTIF(B3:B19,">="&MAX(B3:B19))

B3, copied down:

=IF(A3<>"",IF(ISNUMBER(MATCH(A3,$A$2:A2,0)),"",COUNTIF($A$3:$A$19,A3)),"")

E1:

=MAX(B3:B19)

E3:

=IF(ROWS($E$3:E3)<=$B$1,INDEX($A$3:$A$19,SMALL(IF(B$3:B$19=$E$1,ROW(B$3:B$19)-ROW(B$3)+1),ROWS($E$3:E3))),"")

which is confirmed with control+shift+enter (not with enter) and copied down.

The same output can also be obtained by running Data|PivotTable... on A2:A9 as can be seen in G:H on the exhibit.