# Arrival Pattern Data

#### G-Matriix

##### Board Regular
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.

Thanks

Gmatriix

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Hi -

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

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

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.

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

OK Thanks....You guys are greats!

I will try...and let you know

Thanks again!

Replies
8
Views
1K
Replies
2
Views
191
Replies
2
Views
212
Replies
4
Views
250
Replies
1
Views
234

1,221,521
Messages
6,160,303
Members
451,637
Latest member
hvp2262

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back