Tired of beating my head against the formula bar

[Cubicle_51]

I know a simple answer is staring me in the face I just can't see it....so after searching unsuccessfully across the boards I come asking for help...

We use 8ft sections of tubing to make structures in our warehouse (conveyors, racks and such)

Example: I need 9 pieces cut at 45.5 inches, I know I can get two 45.5 cuts out of one 8ft tube,

How do I get excel to start over at 96 inches (8ft) if the remainder of tube is less than length of cut needed?

Basically, how can I get excel to tell me how many pieces of 8ft tube I need to use if I have to cut (a variable amount) of pieces at a (variable length)

 

[Fluff]

How about

+Fluff 1.xlsm
	H	I	J
2	Qty	Length	Required
3	9	46.5	5
Main

Cell Formulas
Range	Formula
J3	J3	=ROUNDUP(H3*I3/96,0)

 

[Joe4]

How about

+Fluff 1.xlsm
	H	I	J
2	Qty	Length	Required
3	9	46.5	5
Main

Cell Formulas
Range	Formula
J3	J3	=ROUNDUP(H3*I3/96,0)

Fluff,

I was thinking along the same lines, but I don't think that will always work.
For example, take the current example and change the needed length to 40.
The answer the formula returns is 4, but that will not work, because you cannot cut more than 2 40 inch pieces out of any 96 inch piece.
So you still actually need 5 pieces, not 4.

So I don't think we can take it "in total" like that.

I am not sure if the best way is to try to create a UDF in VBA, or some formula that is a bit more complex (not sure exactly what that would look like).

 

[Fluff]

Good point Joe, how about this instead

+Fluff 1.xlsm
	H	I	J
2	Qty	Length	Required
3	9	40	5
Main

Cell Formulas
Range	Formula
J3	J3	=ROUNDUP(H3/INT(96/I3),0)

 

[Joe4]

Good point Joe, how about this instead

+Fluff 1.xlsm
	H	I	J
2	Qty	Length	Required
3	9	40	5
Main

Cell Formulas
Range	Formula
J3	J3	=ROUNDUP(H3/INT(96/I3),0)

That wasn't nearly as complex as I thought!
It makes perfect sense. Don't know why I didn't think of that!

 

[Fluff]

I should have thought of that before.

 

[Cubicle_51]

see I went round and round and never considered INT.

that's absolutely amazing

Thank you both very much!!

 

[Fluff]

Glad we could help & thanks for the feedback.