Mode for Non-Numbers
 

How can I return the most frequently occurring text entry that fits a given
critiera?
E.g.

__A__ __B__
Atlanta John
Atlanta John
Atlanta Mark
Atlanta John
Boston Mark
Boston John
Boston Mark
Boston Mark
Boston Mark

I would like to be able to return IF(A=Atlanta,Mode(B)) = John.

Thanks.

Mode for Non-Numbers
 

There are issues with using a formula if you have tie. To that end I would
tend to use a pivot table... Try this. Put your cursor in the data area and
select Data - Pivot Table. Follow the Wizard. Place the city in the top
criteria area. Place the names in the left hand column and also in the data
area. This will give you a count of the names for each city. Right click on
the Names field and select Field Settings. Click on Advanced and Chang the
Show Top to 1.

As you change the city in the filter section of the pivot table it will
display for you the most frequently occuring name and the number of times it
occured. If there is a tie it will show both names...
--
HTH...

Jim Thomlinson


"Singh" wrote:

How can I return the most frequently occurring text entry that fits a given
critiera?
E.g.

__A__ __B__
Atlanta John
Atlanta John
Atlanta Mark
Atlanta John
Boston Mark
Boston John
Boston Mark
Boston Mark
Boston Mark

I would like to be able to return IF(A=Atlanta,Mode(B)) = John.

Thanks.

Mode for Non-Numbers
 

Appreciate the response.
I'm not particularly worried about ties as there are several thousand pieces
of data. I am trying to avoid pivoting as to reduce the need for any manual
steps to be taken. Any advice would be appreciated.

Thanks.

"Jim Thomlinson" wrote:

There are issues with using a formula if you have tie. To that end I would
tend to use a pivot table... Try this. Put your cursor in the data area and
select Data - Pivot Table. Follow the Wizard. Place the city in the top
criteria area. Place the names in the left hand column and also in the data
area. This will give you a count of the names for each city. Right click on
the Names field and select Field Settings. Click on Advanced and Chang the
Show Top to 1.

As you change the city in the filter section of the pivot table it will
display for you the most frequently occuring name and the number of times it
occured. If there is a tie it will show both names...
--
HTH...

Jim Thomlinson


"Singh" wrote:

How can I return the most frequently occurring text entry that fits a given
critiera?
E.g.

__A__ __B__
Atlanta John
Atlanta John
Atlanta Mark
Atlanta John
Boston Mark
Boston John
Boston Mark
Boston Mark
Boston Mark

I would like to be able to return IF(A=Atlanta,Mode(B)) = John.

Thanks.

Mode for Non-Numbers
 

=INDEX(B1:B9,MODE(IF(A1:A9="Atlanta",MATCH(B1:B9,B 1:B9,0)+{0,0})))

ctrl+shift+enter, not just enter


"Singh" wrote:

How can I return the most frequently occurring text entry that fits a given
critiera?
E.g.

__A__ __B__
Atlanta John
Atlanta John
Atlanta Mark
Atlanta John
Boston Mark
Boston John
Boston Mark
Boston Mark
Boston Mark

I would like to be able to return IF(A=Atlanta,Mode(B)) = John.

Thanks.