Most Common Occurance

Nick147

Board Regular
Joined
Apr 24, 2003
Messages
111
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
=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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Board Regular
Joined
Apr 24, 2003
Messages
111

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi Nick147

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

HTH
PGC
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
ABCDEF
113
22Most Freq Name(s)
3NameFreqNick
4Bob2Benoit
5Nick3 
6Bill1
7Nick 
8Mike1
9Bob 
10Nick 
11Benoit3
12Benoit 
13 
14Benoit 
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.
 

Forum statistics

Threads
1,136,579
Messages
5,676,652
Members
419,638
Latest member
GlenMc52

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