Calculating the total various lengths.

[ldavison10]

Hi,
I need some starter advice on a spreadsheet that can do the following (ill try keep it simple)

Pipes come in 3.0m lengths.

On Area 1 I use 0.7m pipe, 15 times.
On Area 2 I use 1.2m pipe, 7 times.
On Area 3 I use 0.65m pipe, 24 times.

What im trying to achieve is a spreadsheet that calculates how many 3.0m lengths of pipe i need, with the minimum amount of waste.

Can anyone start me off in the right direction.

Many thanks,

Lee

[NBVC]

If your cut lengths are in A1:A3 and your Quantities are in B1:B3 and your pipe length is in C1, then

=Sumproduct(A1:A3,B1:B3)/C1 should give you number of 3.0m lengths you need.

[darkyam]

I disagree, as the lengths he needs the pipes in won't go evenly into 3, meaning there will be waste. To start with, .065*4=2.6, which is as many of those lengths as you can get in a pipe, so you will need six lengths for them. 1.2 + .7*2 =2.6 as well, so you will need 7 lengths cut like that and one more length to handle the last .7m pipe.
Formulaically, since .65 and .7 are nearly the same length and any combination of them will still only fit the same number in a 3m length, you can treat them as equals. I would do something like =B1 (quantity of 1.2m) + ROUNDUP((A1+C1-B1*2)/4,0)

[ldavison10]

Thanks for the replies. I agree with Darkyams formula which nearly matched what i had drafted by hand.
Can you amend the formula so i can put differing lengths in every time (ie not class 0.65 and 0.70 as the same answer.) I have about 20 of these to do and no two are the same!

Thanks
Lee

[NBVC]

Oh well... I must have misinterpreted your request....

[darkyam]

No, that may be possible with solver or some VB script that tests all alternatives, but for a formula, you'll probably have to figure out which combination of lengths most closely fits 3m and come up with a formula. Sorry.