Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.

Get a Professional Answer

Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.

100% Satisfaction Guarantee

Rate the answer you receive.

Ask Robert Your Own Question

Robert, Consultant

Category: Programming

Satisfied Customers: 8999

Experience: years of consulting experience with large and small nyc firms

23372949

Type Your Programming Question Here...

Robert is online now

If I have a large data set of various length, widths, and

Customer Question

If I have a large data set of various length, widths, and heights, how do I build a solver table or other excel tool to provide me 8 sets of L, W, H that would match the majority of the data range? In other words, I am trying to find the 8 box sizes that would cover the majority of LxWxH combinations (over 1 million combinations).

can you explain the real world application it is being used for. that would help me understand the problem a bit better and i can let you know if i can do it and how

I have a set of data with order numbers and product dimensions ( length, width, height). I want to right size my shipping boxes based on products dimensions as best as I can. Trying to narrow down to 8 boxes that would reduce empty space of boxes as much as possible but fit enough products range.

so you would want a box that had larger dimensions in each category than the item itself but as small as possible or within a certain number of inches in any direction ?

probably the second one

than you could make some sort of a query that ran each possible box size against each part and flag how many parts it would be acceptable for. then look at the top boxes on the list. you would still probably want to look over them manually so as to get yourself a good spread otherwise if you have 8000 items that are smaller than a foot and 200 that are larger your top 8 are all going to be under a foot so its not a great solution.

do you have a list of actual dimensions for each of your products because if you are just going on any possible dimensions its also not very useful. the shipping companies already have done that calculation and you can just buy one of everything they sell.

I thought a solver table would be good but I don't know how to do one. There are over 1 million dimensions from the shipping sample I used. I tried pivots and counting statements but it gets complicated because I need the ideal combination of l, w, h so looking at them individually does not work. I tried adding file but it was too big. I can email it if that would help

do you know what you are shipping ? i doubt that you have a million products so you can simplify it that way how many do you have and do you have a list of them with the dimensions in separate easy to read fields. ie just the number one in each or 3 fields for L H W

I have 3 fields that has a number for length, width, height. I used what I shipped because I want to get the majority of what I regularly ship not necessarily items in stock. I didn't want a 10X10X10 item that I rarely ship to impact a 1X1X1 item I ship 20 times a week. I'm trying to get the biggest bang out of my boxes. I'm always going to have outliers.

if you have a list with those 3 fields and a field showing how many you shipped over any particular period that you think is representitive of your business and a list of available standard box sizes i can try to put something together for you but i dont think excel will do it easily and i would probably want about 100 dollars to do it.

Let me think about it. And just to clarify I can make whatever box size I want. There really is no standard. I just want the ones that will fit the most product range.

ok i didnt realize that you could make the boxes any size you want. that makes it a little different but i think you are making it too difficult on yourself. maybe just create groups of anything within 4 inches of each other in every dimension and see what groups you wind up with.