If Statement

[Johnboy28]

Hi, Can someone help with the below formula? Thanks

If less than 42 carton = 1 and divide greater than 42 carton, divide by 42 to get number of pallets required.
Cartons Per Pallet	42
Less than 42	1
Customer	No. Ctn	No. Pallets	Answer
Customer 1	8		1
Customer 2	48		1.14
Customer 3	16		1
Customer 4	13		1

<tbody>
</tbody>

 

[jtakw]

Hi,

Format result cells to number of decimal places as desired:

Book1
	A	B	C	D	E	F
1	Customer	No. Ctn	No. Pallets		Cartons Per Pallet	42
2	Customer 1	8	1	1
3	Customer 2	48	1.142857143	1.14285714
4	Customer 3	16	1	1
5	Customer 4	13	1	1
6	Customer 5	154	3.666666667	3.66666667
7
8			Cell Reference	Hard-coded
Sheet370

Cell Formulas
Range	Formula
C2	=MAX(1,B2/F$1)
D2	=MAX(1,B2/42)

 

[Johnboy28]

Hi,

Format result cells to number of decimal places as desired:

	A	B	C	D	E	F
1	Customer	No. Ctn	No. Pallets		Cartons Per Pallet	42
2	Customer 1	8	1	1
3	Customer 2	48	1.142857143	1.14285714
4	Customer 3	16	1	1
5	Customer 4	13	1	1
6	Customer 5	154	3.666666667	3.66666667
7
8			Cell Reference	Hard-coded

<colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet370​

Worksheet Formulas Cell Formula C2 =MAX(1,B2/F$1) D2 =MAX(1,B2/42) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Thank for your reply.
would it be possible to come up with a formula on below - base on 42 Cartons per pallet?

					Answer
Customer	No. Ctn	No. Full Pallets	No. Parts		No. Full Pallets	No. Parts
Customer 1	8				0	0.190
Customer 2	48				1	0.143
Customer 3	86				2	0.048
Customer 4	13				0	0.310

<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> </tbody>

 

[jtakw]

Sure, your requirements have changed:

Book1
	A	B	C	D	E	F	G
1	Customer	No. Ctn	No. Full Pallets	No. Parts		Cartons Per Pallet	42
2	Customer 1	8	0	0.190476
3	Customer 2	48	1	0.142857
4	Customer 3	86	2	0.047619
5	Customer 4	13	0	0.309524
6	Customer 5	154	3	0.666667
Sheet370

Cell Formulas
Range	Formula
C2	=INT(B2/G$1)
D2	=MOD(B2/G$1,1)

 

[Johnboy28]

Sure, your requirements have changed:

	A	B	C	D	E	F	G
1	Customer	No. Ctn	No. Full Pallets	No. Parts		Cartons Per Pallet	42
2	Customer 1	8	0	0.190476
3	Customer 2	48	1	0.142857
4	Customer 3	86	2	0.047619
5	Customer 4	13	0	0.309524
6	Customer 5	154	3	0.666667

<colgroup><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet370​

Worksheet Formulas Cell Formula C2 =INT(B2/G$1) D2 =MOD(B2/G$1,1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Thank you so much. That works great.

 

[jtakw]

You're welcome.