Logistics Formula

cdowd

New Member
Joined
Jul 4, 2010
Messages
5
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!
 
Last edited:

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Sandeep Warrier

Board Regularmmmm Pizza
Joined
Oct 31, 2008
Messages
2,672
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
ABCDEF
1Item#:# of Cases:Cases per Plt:# of Pallets:Weight per Plt (in lbs)#of trucks
20359271177070168818.34
Sheet1
Excel 2003
Cell Formulas
RangeFormula
F2=ROUNDUP(IF(60*E2>43500,(D2*E2)/43500,D2/60),0)
 

shift-del

Well-known Member
Joined
Aug 28, 2009
Messages
516
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,904
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,484
Messages
5,596,407
Members
414,064
Latest member
Duncthegreat

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