| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Mkae Mode function return "" instead of #N/A when there is no Mode | Excel Discussion (Misc queries) | |||
| Mkae Mode function return "" instead of #N/A when there is no Mode | Excel Discussion (Misc queries) | |||
| Mode | Excel Worksheet Functions | |||
| Combo Box goes to edit mode even if design mode is in OFF position | Excel Discussion (Misc queries) | |||
| coverting answer from Radian mode to degree mode | Excel Worksheet Functions | |||
Hybrid Mode
