Cut Smaller Pieces From Bigger Pieces

[Solon Aquila]

Where I work, I take large remnants of raw material and see if I can cut them down to smaller pieces so we have less waste. I've created a few spreadsheets that allow me to do this a bit more efficiently.
Once I've decided which piece to cut, the meat of the operation lies in being able to get the most pieces from the original block.
It took me a bit to work out but I have a spreadsheet that looks at all possible cutting combinations (how I did it is a separate tab).
The attached image (sorry, I can't get xl2bb to load) shows 6 source to work piece combinations.
Each uses formulas to determine the number of pieces yielded by that combination.
In case multiple combinations yield the same number of pieces, I use 'which leaves the largest individual piece of leftover' as the decider. That's not AS critical, but it gives more options for getting another part from the leftover.
I'm trying to figure out how to write a script that will look at the source piece and work piece dimensions and work out which combination gives the best yield / leftover combination.
I think I can set up variables for each dimension and use that spreadsheet as a basis for figuring out how to do the calculations.
•
So, if I take a look at the formulas I used in the spreadsheet, can I basically assign variables to the results of each calculation, then compare each result variable to determine which is best.
Kind of (currently ignoring largest leftover piece):
Sl= source length cell Pl= piece length cell RA=Result A RC=Result C RF=Result F
Sw=source width cell Pw= piece width cell RB=Result B RD=Result D
Sh=source height cell Ph= piece height cell RC=Result C RE=Result E
(Sl÷Pl) × (Sw÷Pw) × (Sh÷Ph)= RA
(Sl÷Ph) × (Sw÷Pl) × (Sh÷Pw)= RB
(Sl÷Pw) × (Sw÷Ph) × (Sh÷Pl)= RC
(Sl÷Pl) × (Sw÷Ph) × (Sh÷Pw)= RD
(Sl÷Pw) × (Sw÷Pl) × (Sh÷Ph)= RE
(Sl÷Ph) × (Sw÷Pw) × (Sh÷Pl)= RF

In a standard spreadsheet, I would go to the bottom of the results column and say "MAX(RA, RB, RC, RD, RE, RF, RG)" but I don't know how to do that in a script.

So, if it looks like the 'code' above appears to be a workable way to test all of the options, how the heck do I say "pick the max of these" in a script?

TIA

 

[Solon Aquila]

@Solon Aquila , by Basic Excel I meant using standard Excel functions to create formulas within a workbook/worksheet, just as I assume you will have done within your posted image sheet.
More presumptive waffle......
Latest versions of Excel do indeed offer many other 'options' in the way of features for computation, analytics and automation. However, majority of users will only ever need to use a minute proportion of what Excel has to offer. Regarding 'code', I am pretty confident that you are meaning the use of VBA. VBA is the programming language associated with Macros, be they recorded or purpose-written. A well constructed Macro can certainly be a powerful option, especially when looking to automate a repetitive task. However, Macros are not necessarily a silver bullet, solving-wise, compared to using standard Excel.
Have you tested the solution I posted? If not, then test it in an otherwise empty sheet of your workbook. I'm hopeful that might be close to what you are wanting.
Click on the Copy icon at the top left corner of the XL2BB MiniSheet. Then Paste into cell A1 of the empty sheet

You can then enter Block Dimensions in C3:E3 The order of dimension does not matter.
Enter the Piece Dimensions in C4:E4. The order of dimension does not matter.

The best cutting option* should then be automatically displayed below. (Hence why might you need a macro?)
* Based solely on piece yield. Does not include a tie-breaker.

The orange cells are 'helpers' for computational purposes and may be hidden.

If the above does prove to be of value then I will happily try and help you incorporate a tie-breaker if needed.

Regarding offcuts. Volume-wise the offcuts will be the same for similar piece yield.
OffcutVol = BlockVol-(PieceVol*PieceQty)
However, the dimensions of the offcuts may vary dependent upon the relative dimensions of the cuts.

My apologies if I have anything or all of this wrong.

Snakehips,
Thank you! This is great and I finally am starting to grasp the index/match thing... well, I think I am. I've been looking at them but apparently, seeing my actual problem may have started it to gel.
There are three cells that use the match function. One of them looks like this:
=INDEX(H8:H13,MATCH(MAX($G8:$G13),$G8:$G13,0))
If I understand correctly:
INDEX in this case is kind of "we're going to choose the value in one of the cells in column H, rows 8-13" and everything after that is the 'how will we decide which one".
The MATCH portion is three fold:
First, the 'value we are trying to match' is defined in this case as 'the maximum value found in cells G8:G13' We could actually use a value, not a formula, here if we wanted (just not in this case).
Second, "figure out which of the H8:H13 cells is in the same row as the maximum value in G8:G13", though it could easily be set for a different location in a different situation. We'll be using that H8:H13 value.
Third, if we can't find an exact match between the maximum value in G8:G13 and the values in H8:H13, then return an error. There are a couple of other options, but we want an error in this situation.
The formula is repeated in columns I and J with the same actions going on, just using the values in columns I and J to match the maximum of G8:G13.
Either way, this does the trick for not having to display all results and highlighting the best one.
I'll try it out and maybe see if I can use the info you provided to play around with the max leftover size as a tie breaker.
Thank you!

 

[Snakehips]

You are welcome, Glad to hear that it is of benefit.
Your understanding of the Index / Match is spot on. Typically using formula expressions to generate formula parameters.
Good luck with sorting the tie-breaker.
If you need any assistance with that, don't be afraid to shout up.

 

[Snakehips]

My shot at tie-breaking equal piece quantity, based on maximum offcut.

Testing Formula Update.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2		ENTER DIMENSIONS					Helper Columns (Can Hide or Move Elsewhere)				Helper Columns (Can Hide or Move Elsewhere)
3		Block Dims >	91.25	67.5	55			13	10	8		3.5	0	1
4		Piece Dims >	6.75	5.5	9.125		Combo Qtys	16	12	10	Offcuts	3.25	1.5	0
5		Best Cutting Option						10	7	6		0	3.625	0.25
6		Use Block Dim	91.25	67.5	55
7		To Cut Piece Dim	9.125	6.75	5.5		QTY		Piece Dim		TieBreak		Offcuts
8		Cut Qty	10	10	10		936	6.75	5.5	9.125		3.5	1.5	0.25
9		Offcut	0	0	0		910	6.75	9.125	5.5		3.5	3.625	0
10		Total Pieces	1000				960	5.5	6.75	9.125		3.25	0	0.25
11							1000	9.125	6.75	5.5	0	0	0	0
12							896	5.5	9.125	6.75		3.25	3.625	1
13							960	9.125	5.5	6.75		0	0	0
14
Sheet5

Cell Formulas
Range	Formula
H3	H3	=QUOTIENT(C3,C4)
I3	I3	=QUOTIENT(D3,C4)
J3	J3	=QUOTIENT(E3,C4)
H4	H4	=QUOTIENT(C3,D4)
I4	I4	=QUOTIENT(D3,D4)
J4	J4	=QUOTIENT(E3,D4)
H5	H5	=QUOTIENT(C3,E4)
I5	I5	=QUOTIENT(D3,E4)
J5	J5	=QUOTIENT(E3,E4)
L3	L3	=MOD(C3,C4)
M3	M3	=MOD(D3,C4)
N3	N3	=MOD(E3,C4)
L4	L4	=MOD(C3,D4)
M4	M4	=MOD(D3,D4)
N4	N4	=MOD(E3,D4)
L5	L5	=MOD(C3,E4)
M5	M5	=MOD(D3,E4)
N5	N5	=MOD(E3,E4)
C6:E6,N8	D6	=D3
C7:E7	D7	=INDEX(I8:I13,MATCH(MAX($K8:$K13),$K8:$K13,0))
C8:E8	D8	=QUOTIENT(D6,D7)
C9:E9	D9	=INDEX(M8:M13,MATCH(MAX($K8:$K13),$K8:$K13,0))
G8	G8	=H3*I4*J5
H8:J8	H8	=C4
K8:K13	K8	=IF(G8=MAX(G$8:G$13),MAX(L8:N8),"")
L8,N9:N10	L8	=L3
M8:M9	M8	=M4
G9	G9	=H3*I5*J4
H9	H9	=C4
I9	I9	=E4
J9	J9	=D4
L9:L11	L9	=L3
G10	G10	=(H4*I3*J5)
H10	H10	=D4
I10	I10	=C4
J10	J10	=E4
M10,N11,M12	M10	=M3
G11	G11	=H5*I3*J4
H11	H11	=E4
I11:J11	I11	=C4
M11,L12:L13	M11	=M3
G12	G12	=H4*I5*J3
H12:I12	H12	=D4
J12	J12	=C4
N12:N13	N12	=N3
G13	G13	=H5*J3*I4
H13	H13	=E4
I13	I13	=D4
J13	J13	=C4
M13	M13	=M3
C10	C10	=MAX(G8:G13)

 

[Solon Aquila]

My shot at tie-breaking equal piece quantity, based on maximum offcut.

Testing Formula Update.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2		ENTER DIMENSIONS					Helper Columns (Can Hide or Move Elsewhere)				Helper Columns (Can Hide or Move Elsewhere)
3		Block Dims >	91.25	67.5	55			13	10	8		3.5	0	1
4		Piece Dims >	6.75	5.5	9.125		Combo Qtys	16	12	10	Offcuts	3.25	1.5	0
5		Best Cutting Option						10	7	6		0	3.625	0.25
6		Use Block Dim	91.25	67.5	55
7		To Cut Piece Dim	9.125	6.75	5.5		QTY		Piece Dim		TieBreak		Offcuts
8		Cut Qty	10	10	10		936	6.75	5.5	9.125		3.5	1.5	0.25
9		Offcut	0	0	0		910	6.75	9.125	5.5		3.5	3.625	0
10		Total Pieces	1000				960	5.5	6.75	9.125		3.25	0	0.25
11							1000	9.125	6.75	5.5	0	0	0	0
12							896	5.5	9.125	6.75		3.25	3.625	1
13							960	9.125	5.5	6.75		0	0	0
14
Sheet5

Cell Formulas
Range	Formula
H3	H3	=QUOTIENT(C3,C4)
I3	I3	=QUOTIENT(D3,C4)
J3	J3	=QUOTIENT(E3,C4)
H4	H4	=QUOTIENT(C3,D4)
I4	I4	=QUOTIENT(D3,D4)
J4	J4	=QUOTIENT(E3,D4)
H5	H5	=QUOTIENT(C3,E4)
I5	I5	=QUOTIENT(D3,E4)
J5	J5	=QUOTIENT(E3,E4)
L3	L3	=MOD(C3,C4)
M3	M3	=MOD(D3,C4)
N3	N3	=MOD(E3,C4)
L4	L4	=MOD(C3,D4)
M4	M4	=MOD(D3,D4)
N4	N4	=MOD(E3,D4)
L5	L5	=MOD(C3,E4)
M5	M5	=MOD(D3,E4)
N5	N5	=MOD(E3,E4)
C6:E6,N8	D6	=D3
C7:E7	D7	=INDEX(I8:I13,MATCH(MAX($K8:$K13),$K8:$K13,0))
C8:E8	D8	=QUOTIENT(D6,D7)
C9:E9	D9	=INDEX(M8:M13,MATCH(MAX($K8:$K13),$K8:$K13,0))
G8	G8	=H3*I4*J5
H8:J8	H8	=C4
K8:K13	K8	=IF(G8=MAX(G$8:G$13),MAX(L8:N8),"")
L8,N9:N10	L8	=L3
M8:M9	M8	=M4
G9	G9	=H3*I5*J4
H9	H9	=C4
I9	I9	=E4
J9	J9	=D4
L9:L11	L9	=L3
G10	G10	=(H4*I3*J5)
H10	H10	=D4
I10	I10	=C4
J10	J10	=E4
M10,N11,M12	M10	=M3
G11	G11	=H5*I3*J4
H11	H11	=E4
I11:J11	I11	=C4
M11,L12:L13	M11	=M3
G12	G12	=H4*I5*J3
H12:I12	H12	=D4
J12	J12	=C4
N12:N13	N12	=N3
G13	G13	=H5*J3*I4
H13	H13	=E4
I13	I13	=D4
J13	J13	=C4
M13	M13	=M3
C10	C10	=MAX(G8:G13)

I haven't responded to this as I'm still working to comprehend it!! I will get it worked out in my mind but I wanted to make sure to thank you while I work on it.
Solon