If Statement

J

Johnboy28

Board Regular
Joined
Jun 22, 2013
Messages
198
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
Hi, Can someone help with the below formula? Thanks

[TABLE="width: 509"]
<tbody>[TR]
[TD="width: 678, bgcolor: transparent, colspan: 9"]If less than 42 carton = 1 and divide greater than 42 carton, divide by 42 to get number of pallets required.
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Cartons Per Pallet
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE699]#FFE699[/URL] , align: center"]42
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Less than 42
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE699]#FFE699[/URL] , align: center"]1
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: #D9D9D9"]Customer
[/TD]
[TD="bgcolor: #D9D9D9"]No. Ctn
[/TD]
[TD="bgcolor: #D9D9D9"]No. Pallets
[/TD]
[TD="bgcolor: #D9D9D9"]Answer
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Customer 1
[/TD]
[TD="bgcolor: transparent, align: center"]8
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: center"]1
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Customer 2
[/TD]
[TD="bgcolor: transparent, align: center"]48
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: center"]1.14
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Customer 3
[/TD]
[TD="bgcolor: transparent, align: center"]16
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: center"]1
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Customer 4
[/TD]
[TD="bgcolor: transparent, align: center"]13
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: center"]1
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
Hi,

Format result cells to number of decimal places as desired:


Book1
ABCDEF
1CustomerNo. CtnNo. PalletsCartons Per Pallet42
2Customer 1811
3Customer 2481.1428571431.14285714
4Customer 31611
5Customer 41311
6Customer 51543.6666666673.66666667
7
8Cell ReferenceHard-coded
Sheet370
Cell Formulas
RangeFormula
C2=MAX(1,B2/F$1)
D2=MAX(1,B2/42)
 
Upvote 0
jtakw said:
Hi,

Format result cells to number of decimal places as desired:

ABCDEF
CustomerNo. CtnNo. PalletsCartons Per Pallet
Customer 1
Customer 2
Customer 3
Customer 4
Customer 5
Cell ReferenceHard-coded

<colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"]42[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]48[/TD]
[TD="align: right"]1.142857143[/TD]
[TD="align: right"]1.14285714[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]16[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]13[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]154[/TD]
[TD="align: right"]3.666666667[/TD]
[TD="align: right"]3.66666667[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet370​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=MAX(1,B2/F$1)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=MAX(1,B2/42)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
Thank for your reply.
would it be possible to come up with a formula on below - base on 42 Cartons per pallet?

[TABLE="width: 482"]
<colgroup><col width="129" style="width: 97pt; mso-width-source: userset; mso-width-alt: 4717;"> <col width="64" style="width: 48pt;"> <col width="112" style="width: 84pt; mso-width-source: userset; mso-width-alt: 4096;"> <col width="82" style="width: 62pt; mso-width-source: userset; mso-width-alt: 2998;"> <col width="64" style="width: 48pt;"> <col width="104" style="width: 78pt; mso-width-source: userset; mso-width-alt: 3803;"> <col width="86" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3145;"> <tbody>[TR]
[TD="width: 129, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 112, bgcolor: transparent"][/TD]
[TD="width: 82, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 190, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , colspan: 2"]Answer[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]Customer[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]No. Ctn[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]No. Full Pallets[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]No. Parts[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]No. Full Pallets[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]No. Parts[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Customer 1[/TD]
[TD="bgcolor: transparent"]8
[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0.190[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Customer 2[/TD]
[TD="bgcolor: transparent"]48[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]0.143[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Customer 3[/TD]
[TD="bgcolor: transparent"]86[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]0.048[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Customer 4[/TD]
[TD="bgcolor: transparent"]13[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0.310[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Sure, your requirements have changed:


Book1
ABCDEFG
1CustomerNo. CtnNo. Full PalletsNo. PartsCartons Per Pallet42
2Customer 1800.190476
3Customer 24810.142857
4Customer 38620.047619
5Customer 41300.309524
6Customer 515430.666667
Sheet370
Cell Formulas
RangeFormula
C2=INT(B2/G$1)
D2=MOD(B2/G$1,1)
 
Upvote 0
jtakw said:
Sure, your requirements have changed:

ABCDEFG
CustomerNo. CtnNo. Full PalletsNo. PartsCartons Per Pallet
Customer 1
Customer 2
Customer 3
Customer 4
Customer 5

<colgroup><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"]42[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.190476[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]48[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.142857[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]86[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.047619[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]13[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.309524[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]154[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0.666667[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet370​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=INT(B2/G$1)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=MOD(B2/G$1,1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Thank you so much. That works great.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
Dynamic Sheet Queries
Replies
1
Views
288
Vogateer
V
E
  • Question Question
VBA code clean up
Replies
9
Views
593
mumps
mumps
D
  • Solved
ROUNDUP and MIN with IF/AND
Replies
6
Views
218
dreid1011
dreid1011
S
  • Question Question
Sorting: Prioritizing Group retention over Order
Replies
0
Views
75
stretch2011
S
D
  • Solved
If statement to multiply based on range
Replies
5
Views
464
Fluff
Fluff

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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
Back
Top