Finding the most frequent occurances

I have a worksheet in which the cells contain times followed by a 2
digit code. For example "2:30 AG". I was wondering if there is any way
to find the time that occures most, ignoring the 2 digit code.

The long term goal is to be able to determine at what times these
occurances happen and create a graph that represents those peak times.

You could try this:

First, use a "helper" column to extract the time.
With values starting in A1, enter this in B1:

=--LEFT(A1,LEN(A1)-3)

And copy down as needed.

Then, format a cell to "Time", and enter this formula:
=MODE(B1:B100)
Using the actual cell range that you used in ColumnB.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Bear" <[email protected]> wrote in message
news:[email protected]...
> I have a worksheet in which the cells contain times followed by a 2
> digit code. For example "2:30 AG". I was wondering if there is any way
> to find the time that occures most, ignoring the 2 digit code.
>
> The long term goal is to be able to determine at what times these
> occurances happen and create a graph that represents those peak times.
>

Here's an approach that will take ties into consideration...

Assuming that A2:A10 contains your data, try the following...

B2, copied down:

=LEFT(A2,LEN(A2)-2)+0

C2, copied down:

=IF(ISNA(MATCH(B2,$B$1:B1,0)),COUNTIF($B$2:$B$10,B2),"")

D2, copied down:

=IF(N(C2),RANK(C2,$C$2:$C$10)+COUNTIF($C$2:C2,C2)-1,"")

E1: enter 1 (indicating you want the top occurring time or times)

F1:

=MAX(IF(C2:C10=INDEX(C2:C10,MATCH(E1,D2:D10,0)),D2:D10))-E1

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER

G2, copied down:

=IF(ROWS($G$2:G2)<=$E$1+$F$1,INDEX($B$2:$B$10,MATCH(ROWS($G$2:G2),$D$2:$D
$10,0)),"")

Hope this helps!

In article <[email protected]>,
"Bear" <[email protected]> wrote:

> I have a worksheet in which the cells contain times followed by a 2
> digit code. For example "2:30 AG". I was wondering if there is any way
> to find the time that occures most, ignoring the 2 digit code.
>
> The long term goal is to be able to determine at what times these
> occurances happen and create a graph that represents those peak times.

Hi RagDyer
I suppose the
=-- <<< does the work of converting the number
from a string to a number in one step. I
wasn't familiar with its use, but will henceforth,
Thanks,
Jim



"RagDyer" <[email protected]> wrote in message
news:[email protected]...
> You could try this:
>
> First, use a "helper" column to extract the time.
> With values starting in A1, enter this in B1:
>
> =--LEFT(A1,LEN(A1)-3)
>
> And copy down as needed.
>
> Then, format a cell to "Time", and enter this formula:
> =MODE(B1:B100)
> Using the actual cell range that you used in ColumnB.
> --
> HTH,
>
> RD
> ==============================================
> Please keep all correspondence within the Group, so all may benefit!
> ==============================================
>
>
> "Bear" <[email protected]> wrote in message
> news:[email protected]...
>> I have a worksheet in which the cells contain times followed by a 2
>> digit code. For example "2:30 AG". I was wondering if there is any way
>> to find the time that occures most, ignoring the 2 digit code.
>>
>> The long term goal is to be able to determine at what times these
>> occurances happen and create a graph that represents those peak times.
>>
>

This worked thank you both very much!