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

[Legacy 98055]

<CENTER><TABLE ALIGN=CENTER BORDER=1><TR><TD BGCOLOR=#0C266B COLSPAN=11><FONT COLOR=WHITE>Microsoft Excel - ConPkg.xls</FONT></TD></TR><TR><TD BGCOLOR=#D4D0C8 COLSPAN=11>File(<U>F</U>) Edit(<U>E</U>) View(<U>V</U>) Insert(<U>I</U>) Options(<U>O</U>) Tools(<U>T</U>) Data(<U>D</U>) Window(<U>W</U>) Help(<U>H</U>)</TD></TR><TR><TD ALIGN=CENTER COLSPAN=2 BGCOLOR=White>B1</TD><TD ALIGN=CENTER BGCOLOR=#D4D0C8 >=</TD><TD COLSPAN=8 BGCOLOR=White></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER> </TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>B</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>C</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>D</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>E</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>F</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>G</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>H</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>I</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>J</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>K</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>1</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>2</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>3</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF VALIGN=MIDDLE ><FONT FACE=Arial COLOR=#000000>Proctor & Gamble Drop Lot (Weekly Movement Satistics)</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>4</TD><TD BGCOLOR=#C0C0C0> </TD><TD BGCOLOR=#C0C0C0> </TD><TD BGCOLOR=#C0C0C0> </TD><TD BGCOLOR=#C0C0C0> </TD><TD BGCOLOR=#C0C0C0> </TD><TD BGCOLOR=#C0C0C0> </TD><TD BGCOLOR=#C0C0C0> </TD><TD BGCOLOR=#C0C0C0> </TD><TD BGCOLOR=#C0C0C0> </TD><TD BGCOLOR=#C0C0C0> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>5</TD><TD BGCOLOR=#C0C0C0> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=MIDDLE ><FONT FACE=Arial COLOR=#000000>Week Ending Averages based upon the previous 52 weeks</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=MIDDLE ><FONT FACE=Arial COLOR=#000000>Total Movements</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=MIDDLE ><FONT FACE=Arial COLOR=#000000>Average Minutes Per Movement</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=MIDDLE ><FONT FACE=Arial COLOR=#000000>Customer Goal Minutes Per Movement</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=MIDDLE ><FONT FACE=Arial COLOR=#000000>Goal +/(-) Per Move in Minutes</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=MIDDLE ><FONT FACE=Arial COLOR=#000000>Overall Volume Per Man Hour in Movements</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=MIDDLE ><FONT FACE=Arial COLOR=#000000>Shipment Flow Deviation (In Movements) (Per Hour Average) (Floating c/o volume)</FONT></TD><TD BGCOLOR=#C0C0C0> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>6</TD><TD BGCOLOR=#C0C0C0> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#C0C0C0> </TD><TD BGCOLOR=#C0C0C0> </TD><TD BGCOLOR=#C0C0C0> </TD><TD BGCOLOR=#C0C0C0> </TD><TD BGCOLOR=#C0C0C0> </TD><TD BGCOLOR=#C0C0C0> </TD><TD BGCOLOR=#C0C0C0> </TD><TD BGCOLOR=#C0C0C0> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>7</TD><TD BGCOLOR=#C0C0C0> </TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=MIDDLE ><A HREF=javascript:alert('37261')><FONT FACE=Arial COLOR=#000000>5-Jan-02</FONT></A></TD><TD BGCOLOR=#C0C0C0> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>2313</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>7.94</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>15</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=G7-F7')><FONT FACE=Arial COLOR=#000000>7.06</FONT></A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=E7/432')><FONT FACE=Arial COLOR=#000000>5.35</FONT></A></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#C0C0C0> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>8</TD><TD BGCOLOR=#C0C0C0> </TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=MIDDLE ><A HREF=javascript:alert('=C7+7')><FONT FACE=Arial COLOR=#000000>12-Jan-02</FONT></A></TD><TD BGCOLOR=#C0C0C0> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=MIDDLE ><FONT FACE=Arial COLOR=#000000>2502</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=MIDDLE ><FONT FACE=Arial COLOR=#000000>8.02</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=MIDDLE ><FONT FACE=Arial COLOR=#000000>15</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=MIDDLE ><A HREF=javascript:alert('=G8-F8')><FONT FACE=Arial COLOR=#000000>6.98 </FONT></A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=MIDDLE ><A HREF=javascript:alert('=E8/432')><FONT FACE=Arial COLOR=#000000>5.79</FONT></A></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#C0C0C0> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>9</TD><TD BGCOLOR=#C0C0C0> </TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=MIDDLE ><A HREF=javascript:alert('=C8+7')><FONT FACE=Arial COLOR=#000000>19-Jan-02</FONT></A></TD><TD BGCOLOR=#C0C0C0> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=MIDDLE ><FONT FACE=Arial COLOR=#000000>2725</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=MIDDLE ><FONT FACE=Arial COLOR=#000000>7.75</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=MIDDLE ><FONT FACE=Arial COLOR=#000000>15</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=MIDDLE ><A HREF=javascript:alert('=G9-F9')><FONT FACE=Arial COLOR=#000000>7.25 </FONT></A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=MIDDLE ><A HREF=javascript:alert('=E9/432')><FONT FACE=Arial COLOR=#000000>6.31</FONT></A></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#C0C0C0> </TD></TR><TR><TD COLSPAN=11><U>(22)</U></TD></TR></TABLE>
<FONT COLOR=#339966>You can see the formula of cells only click each above hyperlinks</FONT>

<FONT COLOR=#339966 SIZE=1>The above image was automatically written by HtmlMakerVer1.12</FONT>
<FONT COLOR=#339966 SIZE=1>If you want this code, <A HREF=mailto:corosuke@chan.co.jp>click here</A> and Colo will email the file to you. Thanks to Colo and Ivan for this code. ></FONT>
</CENTER>



<CENTER><TABLE ALIGN=CENTER BORDER=1><TR><TD BGCOLOR=#0C266B COLSPAN=6><FONT COLOR=WHITE>Microsoft Excel - Book1</FONT></TD></TR><TR><TD BGCOLOR=#D4D0C8 COLSPAN=6>File(<U>F</U>) Edit(<U>E</U>) View(<U>V</U>) Insert(<U>I</U>) Options(<U>O</U>) Tools(<U>T</U>) Data(<U>D</U>) Window(<U>W</U>) Help(<U>H</U>)</TD></TR><TR><TD ALIGN=CENTER COLSPAN=2 BGCOLOR=White>L3</TD><TD ALIGN=CENTER BGCOLOR=#D4D0C8 >=</TD><TD COLSPAN=3 BGCOLOR=White></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER> </TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>A</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>B</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>C</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>D</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>E</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>1</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Hour</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Planned</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Released</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Deviation</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>2</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>3</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('37381.75')><FONT FACE=Arial COLOR=#000000>5/5/2002 18:00</FONT></A></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>17</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>22</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>5</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>4</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=A3+TIME(1,0,0)')><FONT FACE=Arial COLOR=#000000>5/5/2002 19:00</FONT></A></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>17</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>1</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>16</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>5</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=A4+TIME(1,0,0)')><FONT FACE=Arial COLOR=#000000>5/5/2002 20:00</FONT></A></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>12</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>1</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>11</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>6</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=A5+TIME(1,0,0)')><FONT FACE=Arial COLOR=#000000>5/5/2002 21:00</FONT></A></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>17</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>34</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>17</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>7</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=A6+TIME(1,0,0)')><FONT FACE=Arial COLOR=#000000>5/5/2002 22:00</FONT></A></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>15</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>25</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>10</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>8</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=A7+TIME(1,0,0)')><FONT FACE=Arial COLOR=#000000>5/5/2002 23:00</FONT></A></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>16</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>10</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>6</FONT></TD></TR><TR><TD COLSPAN=6><U>Sheet1</U></TD></TR></TABLE>
<FONT COLOR=#339966>You can see the formula of cells only click each above hyperlinks</FONT>

<FONT COLOR=#339966 SIZE=1>The above image was automatically written by HtmlMakerVer1.12</FONT>
<FONT COLOR=#339966 SIZE=1>If you want this code, <A HREF=mailto:corosuke@chan.co.jp>click here</A> and Colo will email the file to you. Thanks to Colo and Ivan for this code.></FONT>
</CENTER>



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.