Alternative for Nested IF formula

[Luke_1814]

Hi Guys,
Chose the right box to store Part A which has volume of 2.7

Box name(Column A)	Box Volume(Column B)
b1	1
b2	2
b3	3

<tbody>
</tbody>

Based on on above table i would use an if conditions which goes like this,
=IF($F$1<=B2,A2,IF($F$1<=B3,A3,IF($F$1<=B4,A4)))

is there any other alternative instead of using IF function. the above example contains 3 rows, however in reality i would need to apply IF func for 100+ rows. Please help.

 

[FormR]

Part No.	Width	Depth	Height	Weight	Stor Volume	Stor Weight	Storage
A123	1650	180	90	1.01	26.73	1.01	FORMULA

<tbody>
</tbody>

Hi, this doesn't fit in any of them does it? How about posting a few rows of varied example data along with the expected result that you expect for each so we have something to test with?

 

[Luke_1814]

Well based on the dimension of the part it would not be fitting in any of the Storage type. however the formula is fixed to Stor-Vol and Stor-weight and hence it chosing the first storage type that is K203.
Below are some of the results i am expecting :
Sheet 1:

Part No.	Width	Depth	Height		Part Volume	PartWeight	Storage Expected Col I
A123	1440	1320	170		170	12	FORMULA Doesnt Fit
B1234	100	100	100		1	12	K210

<tbody>
</tbody>

Sheet2:

Storage	Width	Depth	Height	Volume	Weight
K210	2500	1200	1300	3900	2000
K211	2500	1200	1850	5550	2000

<colgroup><col><col span="3"><col><col></colgroup><tbody>
</tbody>

Note that there would be more Storage types...
Basically to chose a Storage type the part should satisfy
1. Part Vol <= Storage Volume
2. Part Weight<= Storage Weight
3. Atleast one part should fit based on the dimesnion of both Part and Storage.

 

[FormR]

Below are some of the results i am expecting :

That's still quite a limited number of examples! Here is one option you can try - it will need adapting to your two sheet set-up.

Excel 2013/2016
	A	B	C	D	E	F	G
1	Part No.	Width	Depth	Height	Stor Volume	Stor Weight	Storage
2	A123	1440	1320	170	12	12	No Fit
3	B1234	100	100	100	1	12	K210
4
5
6	Storage	Width	Depth	Height	Volume	Weight
7	K210	2500	1200	1300	3800	2000
8	K211	2500	1200	1850	5550	2000
Sheet1

Cell Formulas
Range	Formula
G2	{=IFERROR(INDEX($A$7:$A$8,MATCH(1,IF(B2<=$B$7:$B$8,IF(C2<=$C$7:$C$8,IF(D2<=$D$7:$D$8,IF(E2<=$E$7:$E$8,IF(F2<=$F$7:$F$8,1))))),0)),"No Fit")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Luke_1814]

The above formuala wouldn't hold good for different orientation of product. i have added two more example for reference

Excel 2013/2016

	A	B	C	D	E	F	G
1	Part No.	Width	Depth	Height	Stor Volume	Stor Weight	Storage
2	A123	1440	1320	170	12	12	No Fit
3	B1234	100	100	100	1	12	K210
4	C1234	2500	1100	1000	12	12	K210
5	D1234	1800	1000	1300	12	12	K211
6	Storage	Width	Depth	Height	Volume	Weight
7	K210	1200	2500	1300	3800	2000
8	K211	1300	2500	1850	5550	2000

<tbody>
</tbody>

​

 

[FormR]

The above formuala wouldn't hold good for different orientation of product.

Hi, that's why I asked for varied examples Are there any more variations? If so please post examples. I've got to run now so if no-one else steps in in the meantime I'll post again tomorrow.

 

[Luke_1814]

Hi, no more variations. Thank for your quick response.

 

[Luke_1814]

Below is the example :
Column details:
B to E is the individual part details
F to H is storage details
O to T max quantity that can be stored in different orientation
I is SUT which has to be selected from sheet XYZ and formula is =IFERROR(INDEX(XYZ!$B$2:$B$188,MATCH(1,INDEX((XYZ!$F$2:$F$188>=H4)*(XYZ!$G$2:$G$188>=I4),0),0)),"Doesn't Fit")
J is max of O to T
K is =IFERROR(INDEX($O$3:$T$3,MATCH(LARGE($O4:$T4,1),$O4:$T4,0)),"")
L to N is dimension of selected SUT from column I. i have used vlookup for it.

Sheet 1

A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
	Individual Part	Storage						Orientation of Storage
Part No.	Length	Depth	Height	Weight	Quantity	Storage Volume	Total Weight	SUT	LE qty	Orientation	SUT Width	SUT Depth	SUT Height	LWH	WLH	HLW	LHW	WHL	HWL
A1234	9.5	9.5	1	0.037	500	0.045125	18.5	A5	70180	LWH	215	280	110	70180	70180	68585	67760	67760	68585
A1235	35	20	1	0.003	1000	0.7	3	A1	1140	HLW	95	130	90	1080	1080	1140	1040	1040	1140
A1236	1280	200	130	5.9	200	6656	1180	Doesn't Fit			#N/A	#N/A	#N/A
A1237	1600	230	1920	2	2	1413.12	4	A15	0	LWH	1000	1200	1300	0	0	0	0	0	0

<tbody>
</tbody>

Sheet 2 : XYZ

SUT	Length	Depth	Height	Volume	Weight
A1	95	130	90	1.1115	6
A2	95	180	90	1.539	8
A3	95	280	90	2.394	13
A15	1000	1200	1300	1560	1000

<tbody>
</tbody>

So currently it checks for column G & H and choses a respective SUT.
Row 1,2 & 3 in sheet 1 provides a better example. In Row 4 although column G& satifies the part doesnt fit in any orientation.

 

[FormR]

Hi, every time you post example data the format changes - it's very confusing!

I'm going to use the layout and data from post 34. If post 37 is describing a new requirement then please try describe that new requirement succinctly using words.

This assumes that your storage locations always have a larger depth than width.

Excel 2013/2016
	A	B	C	D	E	F	G
1	Part No.	Width	Depth	Height	Stor Volume	Stor Weight	Storage
2	A123	1440	1320	170	12	12	No fit
3	B1234	100	100	100	1	12	K210
4	C1234	2500	1100	1000	12	12	K210
5	D1234	1800	1300	1300	12	12	K211
6
7	Storage	Width	Depth	Height	Volume	Weight
8	K210	1200	2500	1300	3800	2000
9	K211	1300	2500	1850	5550	2000
Sheet1

Cell Formulas
Range	Formula
G2	{=IFERROR(INDEX($A$8:$A$9,MATCH(1,IF(MIN(B2:C2)<=$B$8:$B$9,IF(MAX(B2:C2)<=$C$8:$C$9,IF(D2<=$D$8:$D$9,IF(E2<=$E$8:$E$9,IF(F2<=$F$8:$F$9,1))))),0)),"No fit")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Luke_1814]

Hi,
Apologies for confusing earlier.

The above formula shows error and would not hold good in case if Height was greater than width. Please ignore the previous posts. I would request you to help me in layout no. 37. Basically what i actually require is to fit a part in smallest SUT(Storage) possible.

Initially i assumed that by using Volume and Weight of the part i could identify the smallest SUT(Storage). However as you can see, dimensions of the part also plays the role. For ex in row no. 4, although the Storage Volume and weight satisfies, the number of pieces that can be stored is displaying it as zero.

hence i would require a formula where in it should first satisfy whether minimum of 1piece could be stored in SUT and then check whether it satifies both Volume and Weight.

 

[FormR]

would not hold good in case if Height was greater than width.

How about putting each of the storage dimensions in order and each of the product dimensions in order and then applying the formula to those ordered dimensions. For example:

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L
1	Part No.	L	D	H	Wt	Qty	Vol	Wt	Part Dim 1	Part Dim 2	Part Dim 3	SUT
2	A1234	9.5	9.5	1	0.037	500	0.045125	18.5	1	9.5	9.5	A15
3	A1235	35	20	1	0.003	1000	0.7	3	1	20	35	A1
4	A1236	1280	200	130	5.9	200	6656	1180	130	200	1280	No fit
5	A1237	1600	230	1920	2	2	14		230	1600	1920	No fit
6
7
8	SUT	L	D	H	V	Wt	SUT Dim 1	SUT Dim 2	SUT Dim 3
9	A1	95	130	90	1.1115	6	90	95	130
10	A2	95	180	90	1.539	8	90	95	180
11	A3	95	280	90	2.394	13	90	95	280
12	A15	1000	1200	1300	1560	1000	1000	1200	1300
13
Sheet1

Cell Formulas
Range	Formula
I2	=SMALL($B2:$D2,1)
I9	=SMALL($B9:$D9,3)
J2	=SMALL($B2:$D2,2)
K2	=SMALL($B2:$D2,3)
G9	=SMALL($B9:$D9,1)
H9	=SMALL($B9:$D9,2)
L2	{=IFERROR(INDEX($A$9:$A$12,MATCH(1,IF(I2<=$G$9:$G$12,IF(J2<=$H$9:$H$12,IF(K2<=$I$9:$I$12,IF(H2<=$F$9:$F$12,IF(G2<=$E$9:$E$12,1))))),0)),"No fit")}
Press CTRL+SHIFT+ENTER to enter array formulas.