Logistics Formula

[cdowd]

Hi guys,

I work in the logistics department at work and have an excel sheet that lists the following:

<table style="border-collapse: collapse; width: 413pt;" width="549" border="0" cellpadding="0" cellspacing="0"><col style="width: 59pt;" width="78"> <col style="width: 70pt;" width="93"> <col style="width: 68pt;" width="90"> <col style="width: 72pt;" width="96" span="3"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 59pt;" width="78" height="20">
</td> <td style="width: 70pt;" width="93">
</td> <td style="width: 68pt;" width="90">
</td> <td style="width: 72pt;" width="96">
</td> <td style="width: 72pt;" width="96">
</td> <td style="width: 72pt;" width="96">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">A
</td> <td class="xl66" style="border-left: medium none;">B
</td> <td align="right">C
</td> <td class="xl67" align="right">D
</td> <td class="xl68" align="right">E
</td> <td>
</td> </tr> </tbody></table>1 Item#: # of Cases: Cases per Plt: # of Pallets: Weight per Plt:
2 035927 11770.00 70 168 818.3lbs

I want to have a cell after this called "#of trucks". Under that cell I want a formula that can calculate the number of trucks needed to deliver the 168 pallets.

Here is the criteria:
The truck can hold up to 60 pallets per truck or 43,500lbs...whichever comes first.

I tried using a couple imbedded IF functions but couldn't seem to get it to work. Any help would be appreciated. Thanks so much!

 

[Sandeep Warrier]

Hi,

In the solution given below, I am first checking for the weight of 60 pallets. If the number is higher than the weight limit then calculate the number of trucks based on weight otherwise calculate it based on number of pallets. Is this logic OK for you?

Excel Workbook
	A	B	C	D	E	F
1	Item#:	# of Cases:	Cases per Plt:	# of Pallets:	Weight per Plt (in lbs)	#of trucks
2	035927	11770	70	168	818.3	4
Sheet1

Excel 2003

Cell Formulas
Range	Formula
F2	=ROUNDUP(IF(60*E2>43500,(D2*E2)/43500,D2/60),0)

 

[shift-del]

Hello

<table valign="middle" colspan="11" style="font-family: Calibri; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-size: 11px; font-weight: normal; font-style: normal;" border="1" cellpadding="2" cellspacing="0"><colgroup><col width="28pt"><col width="36,75pt"><col width="51pt"><col width="67,5pt"><col width="56,25pt"><col width="99pt"><col width="73,5pt"><col width="55,5pt"><col width="86,25pt"><col width="83,25pt"><col width="54pt"></colgroup><tbody><tr style="background-color: rgb(250, 250, 250);"><td colspan="11" align="middle">Arbeitsblatt mit dem Namen 'Tabelle1'</td></tr><tr style="background-color: rgb(202, 202, 202);"><td>
</td><td align="middle">A</td><td align="middle">B</td><td align="middle">C</td><td align="middle">D</td><td align="middle">E</td><td align="middle">F</td><td align="middle">G</td><td align="middle">H</td><td align="middle">I</td><td align="middle">J</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">1</td><td align="left">Item</td><td align="left"># of Cases</td><td align="left">Cases per Plt.</td><td align="left"># of Pallets</td><td align="left">Weight per Plt. [lbs]</td><td align="left">Pallets/1 Truck</td><td align="left">lbs/1 Truck</td><td align="left"># of Trucks by Plt.</td><td align="left"># of Trucks by lbs</td><td align="left"># of Trucks</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">2</td><td align="left">035927</td><td align="right">11770</td><td align="right">70</td><td align="right">168</td><td align="right">818,3</td><td align="right">60</td><td align="right">43500</td><td align="right">2,8</td><td align="right">3,160331034</td><td align="right">4</td></tr></tbody></table>
<table valign="middle" colspan="2" style="table-layout: auto; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-family: Calibri; font-size: 10px;" border="3" cellpadding="2" cellspacing="0"><colgroup><col width="40pt"><col></colgroup><tbody><tr style="background-color: rgb(238, 170, 170);"><td>Zelle</td><td>Formel</td></tr><tr><td>H2</td><td>=D2/F2</td></tr><tr><td>I2</td><td>=E2*D2/G2</td></tr><tr><td>J2</td><td>=ROUNDUP(MAX(H2,I2),0)</td></tr></tbody></table><table style="font-family: Arial; font-size: 7pt;"><tbody><tr><td style="color: rgb(51, 51, 51);">Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg</td></tr></tbody></table>

 

[mikerickson]

The formula in G2 is
=ROUNDUP(D2/MIN(43500/E2, 60), 0)<table border=1 cellspacing=0>
<tr align="center" bgcolor=#A0A0A0><td width=25> <td width=25><b>A</b><td width=25><b>B</b><td width=25><b>C</b><td width=25><b>D</b><td width=25><b>E</b><td width=25><b>F</b><td width=25><b>G</b></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>1</b><td align="left" bgcolor=#FFFFFF>Item#:<td align="left" bgcolor=#FFFFFF># of Cases:<td align="left" bgcolor=#FFFFFF>Cases per Plt:<td align="left" bgcolor=#FFFFFF># of Pallets:<td align="left" bgcolor=#FFFFFF>Weight per Plt:<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>No. Trucks</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>2</b><td align="right" bgcolor=#FFFFFF>35927<td align="right" bgcolor=#FFFFFF>11770<td align="right" bgcolor=#FFFFFF>70<td align="right" bgcolor=#FFFFFF>168<td align="right" bgcolor=#FFFFFF>818.3 lbs.<td align="left" bgcolor=#FFFFFF><td align="right" bgcolor=#FFFFFF>4</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>3</b><td align="right" bgcolor=#FFFFFF>35927<td align="right" bgcolor=#FFFFFF>11770<td align="right" bgcolor=#FFFFFF>70<td align="right" bgcolor=#FFFFFF>168<td align="right" bgcolor=#FFFFFF>400 lbs.<td align="left" bgcolor=#FFFFFF><td align="right" bgcolor=#FFFFFF>3</tr>
</table>