Most Common Occurance

[Nick147]

How do I create a formula that will return the value (i.e. list of names), that occurs the most in a given array? For example, a formula that would return "Nick" from the following list: Bob, Nick, Bill, Nick, Mike, Bob, Nick.

Thanks,
Nick147

 

[Oaktree]

=INDEX(A1:A7,MATCH(MAX(COUNTIF(A1:A7,A1:A7)),COUNTIF(A1:A7,A1:A7),0)), which is an array formula and must be confirmed with CTRL+SHIFT+ENTER (doing so correctly will result in Excel putting { }'s around your formula in the formula bar)

What about ties?

 

[barry houdini]

If your list is in A1:A10 and you have no blanks in there then try

=INDEX(A1:A10,MODE(MATCH(A1:A10,A1:A10,0)))

if you do have blanks use

=INDEX(A1:A10,MODE(IF(A1:A10<>"",MATCH(A1:A10,A1:A10,0))))

confirmed with CTRL+SHIFT+ENTER

 

[Nick147]

That worked great! Thanks.

 

[Nick147]

I'm having trouble with barry houdini's formula:

=INDEX(A1:A10,MODE(IF(A1:A10<>"",MATCH(A1:A10,A1:A10,0))))

It appears to only work if I have 2 or more cells in the array that have a value greater than "". So I"m having trouble when I only have one cell in the array that has a value greater than "". Can anyone help?

 

[pgc01]

Hi Nick147

Oaktree's solution seems to work even in that case. Have you tried it?

HTH
PGC

 

[Aladin Akyurek]

How do I create a formula that will return the value (i.e. list of names), that occurs the most in a given array? For example, a formula that would return "Nick" from the following list: Bob, Nick, Bill, Nick, Mike, Bob, Nick.

Thanks,
Nick147

ModeOrMostCommonTextItem (Top 1).xls
	A	B	C	D	E	F
1		1			3
2		2			Most Freq Name(s)
3	Name	Freq			Nick
4	Bob	2			Benoit
5	Nick	3
6	Bill	1
7	Nick
8	Mike	1
9	Bob
10	Nick
11	Benoit	3
12	Benoit
13
14	Benoit
15
Sheet1

B1: 1

B2:

=COUNTIF(B4:B15,">="&LARGE(B4:B15,B1))

B4, copied down:

=IF(A4<>"",IF(ISNUMBER(MATCH(A4,$A$3:A3,0)),"",COUNTIF($A$4:$A$15,A4)),"")

E1:

=MAX(Sheet1!B4:B15)

E3:

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

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

 

[Krishnakumar]

Hi,

Try Pivot table.

HTH