Arrival Pattern Data

G-Matriix

Board Regular
Joined
Sep 20, 2006
Messages
139
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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

agihcam

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
Hi -

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

G-Matriix

Board Regular
Joined
Sep 20, 2006
Messages
139
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

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
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

New Member
Joined
Oct 21, 2005
Messages
32
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

Board Regular
Joined
Sep 20, 2006
Messages
139
OK Thanks....You guys are greats!

I will try...and let you know

Thanks again!
 

Forum statistics

Threads
1,141,316
Messages
5,705,703
Members
421,406
Latest member
kluna90

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top