calculate blocks contained in a larger piece of wood

[St Art]

I am trying to set up an Excel spreadsheet to calculate how many pieces of
wood I can cut from a larger piece of wood and what would be the dimensions
of the remaining wood, if any. For example; how many pieces of wood
measuring 1.5" square x 2" length can be cut from a board that is 4.75" wide
x 7" length x 3.5" thick and what are the dimensions of any remaining wood.

I would like to be able to input the overall dimensions of a piece of lumber
and the desired dimensions of the blocks to be cut from it.

I'm having trouble coming up with a solution and being a woodworker and not
a programmer, I'm not sure if Excel can be used for my problem.

Any and all help is gratefully appreciated

 

[GS]

Hi St Art,

You should be able to do this, as long as you're aware of the physics
involved and account for that in your formulas. This is similar to
calculating the amount of material stock needed to produce a quantity of
parts, -just in reverse. The physics are the same either way. Here's what you
need:

1. 3 input fields for the material stock dimensions. These should be "size,
width, length". How they're used is what's important. For example, "size"
would be the thickness in all cases. So a 12" long round piece of material
that's 1" thick has a size value of 1.000". It's length would be 12.000". It
has no width dimension, as does your example, which measures 3.500" x 4.750"
x 7.000". Your formula needs to be flexible enough to handle missing
dimensions when they're not needed.

2. 6 input fields for the yielded product. These will be the same as the
three in step one, plus a field to contain "cut allowance" for each finished
dimension.

In the case of your example yield product, you will require three pieces
from "length", with a 3.500" x 4.750" x (1.000" -(cut allowance)) piece
remaining. This is the result of putting a cut allowance on "length".
<2.000"+?">

From each of the three cut pieces, you will yield 2 more pieces. Again, this
will leave a remaining size for each of 3.500" x 4.750" x (0.500" -(cut
allowance)).

You then have six 1.500" x 2.000" x 4.750" pieces, each yielding 3 items.
The remaining material from these is six 1.500" x 2.000" x (0.250" -(cut
allowance)) pieces. <in this case, probaly nothing will be left>

3. The value for "cut allowance". This is the material consumed by the saw
<or whatever is doing the cutting>.

So, if I understand you correctly, you want to know the sizes of each of the
remaining pieces, and the yield, similar to what I've outlined here. What
complicates it is wanting to know the size of 'leftovers'. You should also be
able to play with the dimension order to conclude how to make your cuts,
based on orientation.

How it (should) work:
Basically, you are calculating the volume of the material stock, then
dividing that by the volume of the yield product, with cut allowance where
required, to get the yield count.

Depending on how you input the dimensions for the yield product, it should
calculate how many area surfaces per piece of material stock in X and Y, and
how many depths in Z.

That's pretty much what I do with costing spreadsheets for parts
manufacturing. As I said, what's difficult is wanting to know the leftover
sizes. We don't care much for what's left over since we order the material
stock to yield the number of pieces we want to run. Leftovers are put into a
"shorts" inventory, which gets considered for use before ordering any new
material.

I think you definitely have your work cut out for you. (no pun intended) If
someone has a solution that calculates how many items of a given volume will
fit into a container of a given size, it may be easily adaptable. You could
ask how to acquire it.

Good luck!
GS

 

[St Art]

Hi GS
I was having trouble visualizing the process but you explained it very well.
as i will always have length, width and thickness on the wood i have, it was
fairly straight forward to work out a process to give me the total pieces
that i can get from a piece of wood. but you're right...trying to figure out
the leftovers will be the hard part...i'll work on it

thanks for your help

St Art

 

[GS]

Hi St Art,

I'm glad to be of help. Thanks for the feedback!

Since you always have 3 dimensions to work with in your material stock, the
formulas will be easier to construct. It seems you're looking for the maximum
yield, so it would be easier if you don't try to account for the leftovers in
your yield calculations. Just recycle them if you can live with that.

If you call me mentioning "orientation" in my previous reply, it's a very
important aspect of the "equation". Visualization is the key. You have to be
able to "visualize" in 3D to determine the maximum yield. For example, the
way I explained it, I went on my natural instinct as to how the pieces should
be oriented within the envelope of your material stock. Fortunately, I was
correct. Any other orientation would have yielded fewer pieces. I verified
this later using solid modelling CAD software. I was able to manually place
each item inside a transparent container representing your material stock. To
have the software do this automatically requires inputting "equations" so it
can make all the necessary calculations to create the different combinations
possible. The "equations" are simply formulas than refer to the three
dimensions, and include the "spacing" between each of item. The "spacing" is
the equivalent to "cut allowance", which your formula ("equation") must be
flexible enough to not include if not needed. For example, the last six
pieces in my example each yield 3 items. Only 2 of them have cut allowance,
as follows:

|<------ 4.750" ------>|
- - - - - - - - - - - - - - - - -
Item[cut]Item[cut]Item 'assumes [cut]=0.125"
- - - - - - - - - - - - - - - - -
1.500+.125+1.500+.125+1.500 = 4.750

The equation would be: =(Item*3)+(cut*2)
You will need to wrap it in an IF() that checks if the 'z' dimension of
Material Stock requires all three cut allowances, indicating a leftover
piece. This would depend on the Yield Item's dim value for 'z'. You could
capture this using MOD() with the 'z' dims as arguments. Don't forget to
include the cut allowance in Item's 'z' value.
** Suggestion: Use named formulas.
This makes understanding your in-cell formulas easier.
You'll need to use POWER() and PI() for area and volume calculations for
both.
You could have these readily available using named formulas. **

Each possible orientation "configuration" (or combination) would have to be
calculated separately to show all the possible results. If you construct your
spreadsheet to require input cells for each material stock dimension, for
each item dimension, and one for cut allowance, you can enter formulas in
other range areas to calculate all possibilities and choose the one you want.
Here's what I mean:

Material Stock Item Yield
x y z x y z
---------------------------------------------------------------
S W L S W L 'formula
goes here
L S W "
W L S "
This orients Item to x=S, y=W, z=L of Material Stock for each dimension
orientation of Item.

Include range areas as follows for each orientation combination:
L S W ... 'and so on... to orient to x=L, y=S,
z=W
| | |--where cell W's formula is: =MaterialWidth
| |-------where cell S's formula is: =MaterialSize
|------------where cell L's formula is: =MaterialLength

Repeat for:
W L S ... 'and so on... to orient to x=W, y=L,
z=S

********************************************
A note about "Item" and "Material Stock" dimensions:
********************************************
1. If any two dims for either are equal, two of each three sets of results
will be the same.

2. If all three dims for either are equal, all results will be the same.

Given that there are 6 sides to each, 2 of each of those are equal if the
pieces are rectangular. This includes square blocks, which are actually
rectangles with all sides equal.

I hope this helps give you a little more to go on. Best of Luck!
Garry