Arrival Pattern Data

[G-Matriix]

Hello Everyone, I am new to this board. So I will start out with a question.

I have a excel sheet of jobs that we get daily. What is included in that data are the times these jobs hit our system.

I wanted to know if there is a easier formula or a VBA code that will allow me to see how many jobs are presented with in a certain time period. In this case it is in a 24 hour period in 15min increments on a daily basis. So this would mean I would 96 lines of criteria to evaluate.

Now, with that being said this is how I attempted to solve the problem. The formula I was using is this:

IF(AND(cell>criteria<cell<criteria,"A",""))


IF(AND(cell>criteria<cell<criteria,"B",""))
and so on...............

Then do a =COUNTIF(range:range,"A","") and the bottom

as you can see this can get bit hairy. Their would be too many formulas to drag.

Is there an easier way? Please help!!!

Thanks

Gmatriix

 

[agihcam]

Hi -

Welcome to the board.
Please post a few sample data and the result you want.

 

[G-Matriix]

OK Thanks!

This is what I am looking at:

Data Sample: (jobs that arrive at these times)
00:30
1:25
1:39
2:15
2:47
3:05
3:45
3:56
(and so on for a 24hr period)

Data needed:

00:00 - 00:15 (how many jobs)
00:15 - 00:30 (how many jobs)
00:30 - 00:45 (how many jobs)
(and so on for a 24 hour period)

Thanks

Gmatriix

 

[agihcam]

still trying to understand what you really wanted, then what should be the qty for these?

00:00 - 00:15 (?)
00:15 - 00:30 (?)
00:30 - 00:45 (?)
based on the above sample.

 

[macajm]

Refer Excel help on the FREQUENCY function.

if data in column A, then in column B enter in successive rows 0:00, 0:15, 0:30 etc down to 23:45 (you can use the autofill down tool).
then copy cells b2:b96 and paste into c1:c95.
Then highlight cells d1:d96 and enter the following formula but use Ctrl+Shift+Enter to enter it as an array formula:-
=FREQUENCY($A:$A,$C$1:$C$95)
if you have done it correctly, Excel should have put braces around the formula to indicate that it is an array formula thus:-
{=FREQUENCY($A:$A,$C$1:$C$95)}

Then the results (counts) should appear in column D

 

[G-Matriix]

OK Thanks....You guys are greats!

I will try...and let you know

Thanks again!