Prev: Print Dialog Box
Next: How do you do an IF statement in Excel comparing three columns
From: Brymor on 2 Feb 2010 11:30
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
From: Gary''s Student on 2 Feb 2010 12:15
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
From: T. Valko on 2 Feb 2010 12:55
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" <Brymor(a)discussions.microsoft.com> wrote in message
news:1CEF1800-F196-4C7E-ACFC-96EFB7CB19FE(a)microsoft.com...
> 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


From: JP on 2 Feb 2010 13:13
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 <Bry...(a)discussions.microsoft.com> 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

From: Anand on 3 Feb 2010 05:50
On Feb 2, 9:30 pm, Brymor <Bry...(a)discussions.microsoft.com> 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
 |  Next  |  Last
Pages: 1 2 3
Prev: Print Dialog Box
Next: How do you do an IF statement in Excel comparing three columns