Probably a simple formula needed here for the simple-minded.

[Tom Schreiner]

Microsoft Excel - ConPkg.xls
File(F) Edit(E) View(V) Insert(I) Options(O) Tools(T) Data(D) Window(W) Help(H)
B1		=
*	B	C	D	E	F	G	H	I	J	K
1	*	*	*	*	*	*	*	*	*	*
2	*	*	*	*	*	*	*	*	*	*
3	*	Proctor & Gamble Drop Lot (Weekly Movement Satistics)	*	*	*	*	*	*	*	*
4	*	*	*	*	*	*	*	*	*	*
5	*	Week Ending Averages based upon the previous 52 weeks	*	Total Movements	Average Minutes Per Movement	Customer Goal Minutes Per Movement	Goal +/(-) Per Move in Minutes	Overall Volume Per Man Hour in Movements	Shipment Flow Deviation (In Movements) (Per Hour Average) (Floating c/o volume)	*
6	*	*	*	*	*	*	*	*	*	*
7	*	:alert('37261')>5-Jan-02	*	2313	7.94	15	:alert('=G7-F7')>7.06	:alert('=E7/432')>5.35	*	*
8	*	:alert('=C7+7')>12-Jan-02	*	2502	8.02	15	:alert('=G8-F8')>6.98	:alert('=E8/432')>5.79	*	*
9	*	:alert('=C8+7')>19-Jan-02	*	2725	7.75	15	:alert('=G9-F9')>7.25	:alert('=E9/432')>6.31	*	*
(22)

You can see the formula of cells only click each above hyperlinks

The above image was automatically written by HtmlMakerVer1.12
If you want this code, click here and Colo will email the file to you. Thanks to Colo and Ivan for this code. >

Microsoft Excel - Book1
File(F) Edit(E) View(V) Insert(I) Options(O) Tools(T) Data(D) Window(W) Help(H)
L3		=
*	A	B	C	D	E
1	Hour	*	Planned	Released	Deviation
2	*	*	*	*	*
3	:alert('37381.75')>5/5/2002 18:00	*	17	22	5
4	:alert('=A3+TIME(1,0,0)')>5/5/2002 19:00	*	17	1	16
5	:alert('=A4+TIME(1,0,0)')>5/5/2002 20:00	*	12	1	11
6	:alert('=A5+TIME(1,0,0)')>5/5/2002 21:00	*	17	34	17
7	:alert('=A6+TIME(1,0,0)')>5/5/2002 22:00	*	15	25	10
8	:alert('=A7+TIME(1,0,0)')>5/5/2002 23:00	*	16	10	6
Sheet1

You can see the formula of cells only click each above hyperlinks

The above image was automatically written by HtmlMakerVer1.12
If you want this code, click here and Colo will email the file to you. Thanks to Colo and Ivan for this code.>




Thanks for the help...
I am not very strong in math.
I need help in Cell J7 of the first example.

Brief description incase you have any better ideas.
All of the numbers(Movements) are truckloads of product.
We are being accused of overstaffing.
We are having to justify hiring more drivers because we do not have
enough manpower to keep up at times.
One day or hour we do nothing, the next we are overwhelmed.
Our customer goes by total volume. That is fine if the production
is steady.
A third party loads the trailers, we move 'em.
The third party plans and ships all of the product.
In the second example you will see loads planned in column C
In column D are the actual number of loads released corresponding
to that specific hour's sceduled time.
The times actually run, per week, on the hour, from Sun 18:00 to
the following Sat 18:00.
I need a number in Cell J7 of the first example which will show
a percentage of flow based upon total volume in the second sheet example.
I am probably missing an obviously easy solution, but I would appreciate
the further input. This kind of stuff get's some attention
outside of my little local office. Plus there is a sort of
duel between our project and the shipper. It's been fun!
Thanks,
Tom

[Mark O'Brien]

Yeah, um, I'm having a problem reading the post (too many beers), but at least this post will bump it back to the top without you doing it yourself.

I've had too many problems with truck drivers, (coincidentally from IL, but named "Don"), to be able to focus on the problem.

[Jay Petrulis]

I'm trying to grasp what you are trying to do, but cannot see it yet.

To take only the first set of numbers, we have =e7*b7/60 = 306 hours or so.

The plan was =e7*g7/60 = 578 hours

Savings of 272 hours for that set.

But, your overall volume per man hour = 5.35 movements, indicating that 432 people-hours are used??? Am I misreading this? Anyway, I'm getting off track, I think.

You are deviating by +7.06 minutes per movement x 2313 movements/60 minutes per hour, which is back to the 272. Would you divide that by the 5.35?

=(E7*H7)/(60*I7)

This doesn't look right to me.

Please provide a little more guidance on what you want to do, and what the above numbers mean.

Thanks,
Jay

[ This Message was edited by: Jay Petrulis on 2002-05-13 20:56 ]

[PDuPre']

If you take the total number of deviations and divide them by the total number of actual shipments (Volume?) you will have a number that shows the relation between the deviations and the total number of shipments.

For instance if there were 17 deviations and 17 total shipments then there would be a 100% deviation. The title for Column J says (In Movements). Since my value is a %, not sure if this solution will satisfy that requirement.

Please note that if the goal is to show why you need additional headcount, it may be better grapsed by showing a second level of deviation of say 2 hours, etc. Or, the release of twice your capacity in one hour when it was scheduled over 3. Just this one stat could be misleading.

Another strategy would be to calculate the cost of each deviation and add up the $. Probably costs more on holidays, week-ends, etc.