Hi all,
I'm looking for an formula with if, or, and:
Situation:
I have a box A: 50 x 30 x 20
I have a box B: 30 x 20 x 50 (or 20 x 50 x 30)
Boxes are the same only L x B x H are in different positions.
I'm looking for a formula that will tell me if box B will fit in Box A
It should take all 3 dimentions into account to see if it fits.
I have a formula but this will olny check 1 side:
=IF(OR(C3>30;D3>30;E3>30);A3;0)
C3 = Lenght
D3 = Width
E3 = Height
A3 = (text) It won't fit
Well technically, for a box to fit in another box you could use their volume (a box like you describe is basically a Parallelepiped - https://en.wikipedia.org/wiki/Parallelepiped#Volume). So if the volume of box A is smaller than the volume of box B, then Box B does not fit in box A and vice versa.
To calculate the volume you need to multiply the length, width and height. If you have the 3 in 3 cells in row A (box A) and B (box B) your formula can then be:
=IF(A1*A2*A3<B1*B2*B3,"It won't fit","It fits")
The formula can be used regardless of where the length, width and height are in the 3 cells. Hope that makes sense.
Alex
Oops see my 3th post... it won't work
That makes perfectly sense! thanks. I feel a Noob now. :-)
Will the formula be the same if I use A as box to fit
and B, C, D... are the boxes that I would like to test if they fit?
Last edited by Milcoi; 07-16-2015 at 10:29 AM.
Box A Box B Box C
30 20 25
50 30 40
20 50 30
it fits it fits
This can't be right?? how does box C fit inside box a?
To know if Box A fits in another box then you can change the formula in one of 2 ways:
=IF(A1*A2*A3<B1*B2*B3,"It fits","It won't fit")
OR
=IF(A1*A2*A3>B1*B2*B3,"It won't fit","It fits")
The way IF works is like this: =IF(logical_test,value_if_true,value_if_false)
In your case, the logical_test is comparing the volumes of the 2 boxes.
If you want to see if different boxes fit in one box (let's say in box A), then make sure to lock the cells for box A:
=IF($A$1*$A$2*$A$3<B1*B2*B3,"It won't fit","It fits")
Alex
A--B--C
30 20 25
50 30 40
20 50 30
-- it fits it fits
Box C will never fits If you look at the L, W, H.
My logic is flawed. I apologize.
Maybe try this, though I may be overcomplicating matters with this one. It should work:
=IF(AND(MAX($A1,$A2,$A3)>=MAX(B1,B2,B3),MIN($A1,$A2,$A3)>=MIN(B1,B2,B3),SUM($A1:$A3)-MAX($A1,$A2,$A3)-MIN($A1,$A2,$A3)>=SUM(B1:B3)-MAX(B1,B2,B3)-MIN(B1,B2,B3)),"Fits","Doesn't fit")
Alex
Last edited by AlexandraT; 07-16-2015 at 10:47 AM.
Creating a formula for this is very easy but I don't understand which box you want to see fits inside which other box. Your description talks about two boxes but your data examples show three boxes and doesn't match your description. When you show A, B, and C, which do you want to fit into which?
If you have two boxes A and B with data as shown in cells A1:C4
Dimension Box A Box B Length 30 20 Width 50 30 Height 20 50
Use this formula to show whether box B fits inside box A:
=IF(OR(C1>B1;C2>B2;C3>B3),"DOES NOT FIT", "FITS")
Hi jeff,
I have 1 box (A) where other boxes should fit into
I have 2 (or more) boxes (B, C) that should lookup if they will fit into box A
Box A--Box B--Box C
30-----20-----20
50-----30-----50
20-----50-----30
-------No-----No
This Is what I want to do. As you can see Box B and C won't fit according to the formula but if you try it (fisicaly) they will.
The lengt, width and height can be set on different position but eventualy will be the same box.
you now understand better?
For each box you have 6 different options to messure:
L x H x W
30x50x20
30x20x50
50x30x20
50x20x30
20x30x50
20x50x30
So it should take all 6 possibilities to try if they will fit into another box
Is this correct?
I made some random box dimensions between 10 and 50, calculated the volume and sorted it by volume. Then the formula only checks boxes with larger volume.
Array formula, has to be entered with Ctrl + Shift + Enter.Formula:
Please Login or Register to view this content.
Last edited by Jacc; 07-16-2015 at 11:49 AM.
<----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left
If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.
I haven't taken the time to figure out how Jacc's solution works or whether it's what you need, but here is a simple solution that is specifically targeted towards your examples and description.
This example determines if box B, C, D.... will fit into A in some orientation. It assumes that a box cannot fit inside another box of identical size.
Both of you have a sollution that is working. Jacc's is not totaly clear for me and with yours I wonder if it is possible to let other boxes fit with same sizes.
I got it working. (example is not correct, sorry)
Thanks for the help. With the last 2 examples I should be able to have it working.
Last edited by Milcoi; 07-17-2015 at 02:53 AM.
To be honest I got a bit carried away with the solution to the "general" box problem he he..., it was fun.
Here it is again, a bit easier to understand maybe. Random numbers for the purpose of testing, just hit F9 to get new values. A bit mesmerizing.
Thanks all,
please see attached the working solution.
Goal:
to get an estimate how many and which boxes are needed to stack the product/items.
Idealy it would only show me what Box and amount needed to stack all products.
Something like this?
Wow! yes thanks that's pretty much how I would like to make the example.
I can continue from here and give some more accurate amount of type and amount of boxes needed.
This example is based on volume but can used as a good first advice for the warehouse.
I'm also busy with the 3D stacking problem. see search engines for more info about it.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks