Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Establishing most common word in an Excel column
Using Office 2003.
In my weatherstation data, [very long listings over 12 months x 4 readings per day ], the wind's direction changes are listed as:- NORTH, NE,SE,SE,EAST,ENE,WSW,SOUTH,SE,EAST, NW, WNW, and so on. I would like to find a formula that will tell me the most prolific word in the column list. I hope that someone can help me here. Thanks and kind regards, Brymor |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Establishing most common word in an Excel column
Can there be moe than one word in a single cell?
-- Gary''s Student - gsnu201001 "Brymor" wrote: Using Office 2003. In my weatherstation data, [very long listings over 12 months x 4 readings per day ], the wind's direction changes are listed as:- NORTH, NE,SE,SE,EAST,ENE,WSW,SOUTH,SE,EAST, NW, WNW, and so on. I would like to find a formula that will tell me the most prolific word in the column list. I hope that someone can help me here. Thanks and kind regards, Brymor |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Establishing most common word in an Excel column
With your data in the range A2:A13, this array formula** returns SE as the
most frequent TEXT entry: =INDEX(A2:A13,MODE(MATCH(A2:A13,A2:A13,0))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Note that if there are an equal number of entries for more than one direction the formula will return the direction that appears first from top to bottom. For example: NE NE SW SW SSW The formula result will be NE. Both NE and SW appear the most often but NE appears first in the list. -- Biff Microsoft Excel MVP "Brymor" wrote in message ... Using Office 2003. In my weatherstation data, [very long listings over 12 months x 4 readings per day ], the wind's direction changes are listed as:- NORTH, NE,SE,SE,EAST,ENE,WSW,SOUTH,SE,EAST, NW, WNW, and so on. I would like to find a formula that will tell me the most prolific word in the column list. I hope that someone can help me here. Thanks and kind regards, Brymor |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Establishing most common word in an Excel column
In addition to what Biff suggested, perhaps a Pivot Table would also
produce what you need? http://www.contextures.com/xlPivot05.html#TopItems --JP On Feb 2, 11:30*am, Brymor wrote: Using Office 2003. In my weatherstation data, [very long listings *over 12 months x 4 readings per day ], *the wind's direction changes are listed as:- NORTH, NE,SE,SE,EAST,ENE,WSW,SOUTH,SE,EAST, NW, WNW, and so on. I would like to find a formula that will tell me the most prolific word in the column list. I hope that someone can help me here. Thanks and kind regards, Brymor |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Establishing most common word in an Excel column
On Feb 2, 9:30*pm, Brymor wrote:
Using Office 2003. In my weatherstation data, [very long listings *over 12 months x 4 readings per day ], *the wind's direction changes are listed as:- NORTH, NE,SE,SE,EAST,ENE,WSW,SOUTH,SE,EAST, NW, WNW, and so on. I would like to find a formula that will tell me the most prolific word in the column list. I hope that someone can help me here. Thanks and kind regards, Brymor You can also use Countif. Assuming your data is in Column A rows 1 to 24000 then in column B if you enter =COUNTIF($B$2:$B$24000, B2) and drag it through the column the number of instances a particular value repeats would be listed you can then choose for the largest number to see which direction the wind was most of the times. Hope that helps, Anand 9910548139 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Establishing most common word in an Excel column
Gary, tks for your response.
There are only random repeats of the sixteen different compas points, form NORTH around to NNW. So, each abbreviation can be classed as one word in each cell -- Thanks and kind regards, Brymor "Gary''s Student" wrote: Can there be moe than one word in a single cell? -- Gary''s Student - gsnu201001 "Brymor" wrote: Using Office 2003. In my weatherstation data, [very long listings over 12 months x 4 readings per day ], the wind's direction changes are listed as:- NORTH, NE,SE,SE,EAST,ENE,WSW,SOUTH,SE,EAST, NW, WNW, and so on. I would like to find a formula that will tell me the most prolific word in the column list. I hope that someone can help me here. Thanks and kind regards, Brymor |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Establishing most common word in an Excel column
Hi,
Assume that your data in in range A2:A20000 (the heading is in A2). In B3, type =countif(A$3:A20000,A3) and copy till B20000. In B2, type condition. In E2, type condition and in E3, type =max(B3:B20000) Select cell G2 and go to Data Advanced Filter Copy to another location. In list range, select A2:B20000. In criteria range, select E2:E3. In Copy, select G2. Check the box for unique records only. Click on OK This should get you the desired result. Please note that if the data changed, you will have to rerun the advanced filter. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Brymor" wrote in message ... Using Office 2003. In my weatherstation data, [very long listings over 12 months x 4 readings per day ], the wind's direction changes are listed as:- NORTH, NE,SE,SE,EAST,ENE,WSW,SOUTH,SE,EAST, NW, WNW, and so on. I would like to find a formula that will tell me the most prolific word in the column list. I hope that someone can help me here. Thanks and kind regards, Brymor |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Establishing most common word in an Excel column
Many thanks Biff, works a treat!
Just copied / pasted your formula into cell, changed the range details and BINGO, I got what I wanted. -- Thanks and kind regards, Brymor "T. Valko" wrote: With your data in the range A2:A13, this array formula** returns SE as the most frequent TEXT entry: =INDEX(A2:A13,MODE(MATCH(A2:A13,A2:A13,0))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Note that if there are an equal number of entries for more than one direction the formula will return the direction that appears first from top to bottom. For example: NE NE SW SW SSW The formula result will be NE. Both NE and SW appear the most often but NE appears first in the list. -- Biff Microsoft Excel MVP "Brymor" wrote in message ... Using Office 2003. In my weatherstation data, [very long listings over 12 months x 4 readings per day ], the wind's direction changes are listed as:- NORTH, NE,SE,SE,EAST,ENE,WSW,SOUTH,SE,EAST, NW, WNW, and so on. I would like to find a formula that will tell me the most prolific word in the column list. I hope that someone can help me here. Thanks and kind regards, Brymor . |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Establishing most common word in an Excel column
Thanks JP,
Biff's suggestion worked OK, but will also try your suggestion, once I've learnt about Pivot tables. Wish me Luck! -- Thanks and kind regards, Brymor "JP" wrote: In addition to what Biff suggested, perhaps a Pivot Table would also produce what you need? http://www.contextures.com/xlPivot05.html#TopItems --JP On Feb 2, 11:30 am, Brymor wrote: Using Office 2003. In my weatherstation data, [very long listings over 12 months x 4 readings per day ], the wind's direction changes are listed as:- NORTH, NE,SE,SE,EAST,ENE,WSW,SOUTH,SE,EAST, NW, WNW, and so on. I would like to find a formula that will tell me the most prolific word in the column list. I hope that someone can help me here. Thanks and kind regards, Brymor . |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Establishing most common word in an Excel column
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Brymor" wrote in message ... Many thanks Biff, works a treat! Just copied / pasted your formula into cell, changed the range details and BINGO, I got what I wanted. -- Thanks and kind regards, Brymor "T. Valko" wrote: With your data in the range A2:A13, this array formula** returns SE as the most frequent TEXT entry: =INDEX(A2:A13,MODE(MATCH(A2:A13,A2:A13,0))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Note that if there are an equal number of entries for more than one direction the formula will return the direction that appears first from top to bottom. For example: NE NE SW SW SSW The formula result will be NE. Both NE and SW appear the most often but NE appears first in the list. -- Biff Microsoft Excel MVP "Brymor" wrote in message ... Using Office 2003. In my weatherstation data, [very long listings over 12 months x 4 readings per day ], the wind's direction changes are listed as:- NORTH, NE,SE,SE,EAST,ENE,WSW,SOUTH,SE,EAST, NW, WNW, and so on. I would like to find a formula that will tell me the most prolific word in the column list. I hope that someone can help me here. Thanks and kind regards, Brymor . |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Establishing most common word in an Excel column
Good luck to you!
--JP On Feb 3, 7:21*am, Brymor wrote: Thanks JP, Biff's suggestion worked OK, but will also try your suggestion, once I've learnt about Pivot tables. Wish me Luck! -- Thanks and kind regards, Brymor |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Determine if 2 cells share a common word | Excel Worksheet Functions | |||
Stumped on establishing web querries on MS Excel | Excel Discussion (Misc queries) | |||
In Excel 07, how do I merge two sprdshts with a common column? | Excel Worksheet Functions | |||
Establishing Excel default toolbars | Excel Discussion (Misc queries) | |||
what three features in word,excel and powerpoint have n common? | New Users to Excel |