Excel Formula to choose Box size

[yimingtan]

Hi All,

I have a list of item(with info on the qty and their size) which I need select suitable box base on size and qty to fit it in.
Example cases:

Standard Box list(all item ignore their height):
Use box ABC(1): If the qty equal or less than 10, and the item size smaller than 10cmx10cm
Use box ABC(10): If the qty more than 10, and the item size smaller than 10cmx10cm

Use box DEF(1): If the qty equal or less than 10, and the item size smaller than 30cmx25cm
Use box DEF(10): If the qty more than 10, and the item size smaller than 30cmx25cm

Use box XYZ(1): If the qty equal or less than 10, and the item size smaller than 50cmx50cm
Use box XYZ(10): If the qty more than 10, and the item size smaller than 50cmx50cm


If I have an item name Z22(with qty 2 and size of 5cmx5cm): the suitable box for this item will be ABC(1)
as the qty less than 10 and the size less than 10cmx10cm

If I have an item name alpha01(with qty 20 and size of 24cmx33cm): the suitable box for this item will be XYZ(10)
as the qty more than 10 and the size larger than 30cmx25cm

Any coding that you guys can advice? as I really run out of idea to create the coding.

 

[palaeontology]

can you please indicate what column(s) you have your data in.

For example, what column is the quantity entered in?

Is your box size entered in one cell (eg: 30cm x 25cm) or across two cells (eg: 30cm in one cell and 25cm in a different cell)

will the unit 'cm' be found with the box size (eg: 30cm) or will it just be a unitless numeric size (eg: 30)

 

[yimingtan]

Hi palaeontology,

Dimension will be on E5 and F5(dimension will be entered across two cells), quantity will be on D5.
all dimension are unit-less. The target is to let excel help me to select the suitable box and print out the box number（eg: ABC(10), XYZ(1)) on cell G5.

can you please indicate what column(s) you have your data in.

For example, what column is the quantity entered in?

Is your box size entered in one cell (eg: 30cm x 25cm) or across two cells (eg: 30cm in one cell and 25cm in a different cell)

will the unit 'cm' be found with the box size (eg: 30cm) or will it just be a unitless numeric size (eg: 30)

 

[palaeontology]

If quantity is in D5 (and down) and the two dimensions are in E5 and F5 (and down), then in G5 (and drag down) the following formula ... =IF(AND(D5="",E5="",F5=""),"",IF(AND(D5<=10,E5<10,F5<10),"ABC(1)",IF(AND(D5>10,E5<10,F5<10),"ABC(10)",IF(AND(D5<=10,E5<30,F5<25),"DEF(1)",IF(AND(D5>10,E5<30,F5<25),"DEF(10)",IF(AND(D5<=10,E5<50,F5<50),"XYZ(1)","XYZ(10)"))))))

Kind regards,

Chris

 

[yimingtan]

Hi Chris,


Thanks for the coding, it works as I wanted!
just that I found out some problem:


Let say I have an item name Alpha with qty of 15 pcs, and the dimension is 20 x 28 cm,


If I insert cell E5=20 and F5=28, the Box result will be XYZ(10)<<<<which is not the correct box


If I insert Cell E5=28 and F5=20, the Box result will be DEF(10)


Not sure any way to solve this?

If quantity is in D5 (and down) and the two dimensions are in E5 and F5 (and down), then in G5 (and drag down) the following formula ... =IF(AND(D5="",E5="",F5=""),"",IF(AND(D5<=10,E5<10,F5<10),"ABC(1)",IF(AND(D5>10,E5<10,F5<10),"ABC(10)",IF(AND(D5<=10,E5<30,F5<25),"DEF(1)",IF(AND(D5>10,E5<30,F5<25),"DEF(10)",IF(AND(D5<=10,E5<50,F5<50),"XYZ(1)","XYZ(10)"))))))

Kind regards,

Chris

 

[palaeontology]

without changing a whole lot of things, could you not just always enter the larger dimension before the smaller dimension ?