Looking for formula

[dive_dive]

I am searching for a formula that will provide a quantity for materials based
on sum of lengths. There will need to be a parameter giving the length of
the base material. For example, if pipe comes in a twenty foot length and I
am cutting this into a 9 foot, 12 foot, and 6 foot length I will need 2
pieces of pipe. Is there a formula that will allow me to automate this? It
will actually be in inches but I used a simplified example. Any help would
be appreciated.

 

[tim m]

You could have your base length of pipe in cell A2 (240 inches) which you
could change as required if you had different lengths.

In Column B you could enter your required cut lengths in inches. (B2 = 108,
B3 = 144, B4 = 72) I would also add zeros down this column to about B20 (so
that you have room to add more cuts if required.)

In Column C2 enter this formula =ROUNDUP(SUM(B2:B20)/A2,0)

It should sum up the total amount of pipe you need in inches and then divide
it by your base length and round up. Give it a whirl and see if it is what
you want.

(The reason I am starting in Row 2 for all of this is because I assume you
would have some headings in row 1. If per chance you are enter feet but
calculate by inches you could also stick columns in that would convert from
feet to inches.)

 

[dive_dive]

Works like a champ!!! Thanks Tim.

You could have your base length of pipe in cell A2 (240 inches) which you
could change as required if you had different lengths.

In Column B you could enter your required cut lengths in inches. (B2 = 108,
B3 = 144, B4 = 72) I would also add zeros down this column to about B20 (so
that you have room to add more cuts if required.)

In Column C2 enter this formula =ROUNDUP(SUM(B2:B20)/A2,0)

It should sum up the total amount of pipe you need in inches and then divide
it by your base length and round up. Give it a whirl and see if it is what
you want.

(The reason I am starting in Row 2 for all of this is because I assume you
would have some headings in row 1. If per chance you are enter feet but
calculate by inches you could also stick columns in that would convert from
feet to inches.)