OT: excel spreadsheet guru's -

[Mikej]

So lets say I am trying add up already determined lengths of pipes that all need to equal up to the same length total.
Ex. I have a 21ft long room, and a stack of 10 pipes to choose from that are 10'11", 9'1", 13" and 18'1" 12' and 6'9". I need to fit a 12" pipe between any two of these other longer pipes to + or minus 3" and fit within the up to 21' 3" or as little as 20'9" long room. Is there a formula anybody could point me to?? This would happen with a heck off a lot of other pipes, too. Thanks if anybody can relate.

[yngpunk]

Originally Posted by Mikej

So lets say I am trying add up already determined lengths of pipes that all need to equal up to the same length total.
Ex. I have a 21ft long room, and a stack of 10 pipes to choose from that are 10'11", 9'1", 13" and 18'1" 12' and 6'9". I need to fit a 12" pipe between any two of these other longer pipes to + or minus 3" and fit within the up to 21' 3" or as little as 20'9" long room. Is there a formula anybody could point me to?? This would happen with a heck off a lot of other pipes, too. Thanks if anybody can relate.

Sounds like the "Solver" would work

[Chief]

Let x and y be the lengths of two pipes such that
x+y+1=/<21.25 or equivalently x+y=/<20.25 and x+y=/>19.75. In the x-y plane the values of x and y satisfying these inequalities is defined by the trapezoidal area bounded by the two limiting right triangles defined by the = sign in the above inequalities. One can select a value for x and the inequalities yield the corresponding range of lengths y satisfying the inequalities; i.e.,
19.75-x=/<y=/<20.25-x

I hope that I haven't done someone's homework.

[Jaybee]

Solver is probably the way to go, though I don't have a ton of experience with it.

If not, you will be stuck with a long string of "SUM" and "IF" functions for every possible combination of pipe lengths. Probably long enough to bog down Excel, which doesn't seem to like more than 8-10 functions in a single formula.

[MattTuck]

It sounds like this is a practical question, but I cannot think of a real life application of it. Can you give me more info on the situation?

Also, how many pipes do you have, and what are the lengths of all the pipes that you have? or will you not know them until later, and want to figure out how best to deal with each new batch.

I think you might be able to do it with solver.

Though, you could probably also do it as a linear optimization. Since you're probably trying to minimize waste.

Give us a bit more detail SOunds like a fun puzzle.

[54ny77]

get the turbo encapsulator add-on for excel. then hit f9.

[MattTuck]

The more I think about this, the less I think Excel is the right application to solve this, but I am waiting on more details.

[Mikej]

Originally Posted by MattTuck

The more I think about this, the less I think Excel is the right application to solve this, but I am waiting on more details.

It’s exactly as described - but 300 pipes of different lengths. Same room size.

[MattTuck]

Are you trying to maximize the number of combinations that fall in the desired range?

What is the shortest and longest pipe in the mix? Or will you not know that ahead of time?

Is there replacement? (ie. is this a theoretical question of how many combinations can be formed with the given list of lengths) OR once a pipe has been used, it is no longer in the mix to be used again?

If there isn't replacement (ie. once a pipe is used, it is no longer available), is the goal to find the maximum number of combinations that add to the desired range, or are you just happy to find a bunch of combinations that work?

This number of possible combinations with 300 pipes seems very very high to me. Like, a huge number.... like bigger than a private bitcoin address.

[Bob Ross]

Originally Posted by 54ny77

get the turbo encapsulator add-on for excel. then hit f9.

I have no idea what that sentence means -- or whether it is even a serious response, or just a sarcastic joke -- but I swear the next time anybody asks me a question I'm going to use that as my answer!

[tourmalet]

This is called the bin-packing problem (in one dimension), as it is analogous to the problem of packing full a bin of a given size with a subset of given packages of known size. It can be solved using the built-in Excel Solver.

There is ample information online on both the problem and how to implement it in Excel.

[ORMojo]

Originally Posted by tourmalet

This is called the bin-packing problem (in one dimension), as it is analogous to the problem of packing full a bin of a given size with a subset of given packages of known size. It can be solved using the built-in Excel Solver.

There is ample information online on both the problem and how to implement it in Excel.

Darn - from the moment I read the OP, this was my response, and then I scroll down the page to find it already offered!

[559Rando]

There are probably many ways to do this, but I'd make good use of the MOD function here and maybe build a recursive function to do the math on array of pipe lengths.

[Louis]

Shouldn't be hard using the Excel Solver.

You can either create equations defining what you want in the spreadsheet and boil it all down to the value of a given cell, or you can insert some "Constraints" directly into the Solver and go from there. I'd probably use a mix of both, with the pipe lengths in the sheet and create equations there and constraints in the Solver.

Good Luck

[Mikej]

Thanks for the replies -