Finding the most frequent occurances

B

Bear

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.
 
R

RagDyer

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!
==============================================
 
D

Domenic

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!
 
J

Jim May

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
 
Top