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.

Finding the most frequent occurances
 

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" wrote in message
oups.com...
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.

Finding the most frequent occurances
 

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,B 2),"")

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,MATC H(ROWS($G$2:G2),$D$2:$D
$10,0)),"")

Hope this helps!

In article .com,
"Bear" 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.

Finding the most frequent occurances
 

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" wrote in message
...
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" wrote in message
oups.com...
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.

Finding the most frequent occurances
 

This worked thank you both very much!