Share via

Help with container calculation.

Anonymous
2013-06-14T18:11:13+00:00

I have two container sizes that I can use to send shipments, a 40-foot (9,315,000 pieces or 3105 units) and a 20-foot (4,050,000 pieces or 1350 units).  I can mix the containers, so that they are filled as completely as possible.  What I need is a formula that will calculate the containers that I need for a shipment.  An example is I have 13,365,000 pieces that I need to send.  This will take one 40-foot (9,315,000) and one 20-foot (4,050,000) containers.  At this time, I have the following in a cell to help me calculate this and I know that there is a better way.

<br>**** <br>(S) <br> (T) <br>(U) <br>(V) <br>(W)
<br><br><br><br><br><br><br><br><br><br><br>(1) <br>Shipping Amounts (Millions) <br>Shipping Amounts (Units) <br>Approx . Pallets of POS (135 units) <br>40 ft Ctr**(3105 Units) (23 Pallets) (9.315 M)** <br>20 ft Ctr**(1350 units) (10 pallets) (4.05 M)**
<br>(2) <br>13,365,000 <br>4455 <br>33 <br>1.43<br><br><br><br><br>=IFERROR(T2/3105, "") **** <br>3.30<br><br><br><br><br>=IFERROR(T23/1350, "")

So, the only thing it tells me is yes, I need one 40-foot.  I then need to calculate if I need an additional 40-foot or a 20-foot.  Any help you can provide me would be appreciated.

Thank you,

Cassandra

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2013-06-14T18:35:21+00:00

    Cassandra wrote:

    I have two container sizes that I can use to send shipments, a 40-foot (9,315,000 pieces or 3105 units) and a 20-foot (4,050,000 pieces or 1350 units).  I can mix the containers, so that they are filled as completely as possible.  What I need is a formula that will calculate the containers that I need for a shipment.  An example is I have 13,365,000 pieces that I need to send.  This will take one 40-foot (9,315,000) and one 20-foot (4,050,000) containers.  At this time, I have the following in a cell to help me calculate this and I know that there is a better way.

    **** (S) (T) (U) (V) (W)
    <br><br><br><br><br><br>(1) Shipping Amounts (Millions) Shipping Amounts (Units) Approx . Pallets of POS (135 units) 40 ft Ctr**(3105 Units) (23 Pallets) (9.315 M)** 20 ft Ctr**(1350 units) (10 pallets) (4.05 M)**
    (2) 13,365,000 4455 33 1.43<br><br><br>=IFERROR(T2/3105, "") **** 3.30<br><br><br>=IFERROR(T23/1350, "")

     

    So, the only thing it tells me is yes, I need one 40-foot.  I then need to calculate if I need an additional 40-foot or a 20-foot.

    Ostensibly, perhaps the following does what you want:

    In V2:

    =IFERROR(INT(T2/3105),"")

    In W2:

    =IFERROR(ROUNDUP(MOD(T2,3105)/1350,0),"")

    However, that might not provide an "optimal" result, depending on your definition of "optimal".  For example, suppose you have 4050 units (1350*3).  The formulas above will result in 1 in V2 and 1 in W2 with significant "waste" in the latter.  If you are happy with that answer, fine.  But under some conditions, 0 in V2 and 3 in W2 might be the preferred result.

    PS:  It is unclear why you use IFERROR in your original formulas.  I retained it just in case there is a good reason (e.g. T2 might be the null string).

    0 comments