excel formula to create a cutting list help needed please

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I dont know if this is the right place to ask this question but i am looking
for a simple excel formula to create a cutting list.

ie

1200
1400
1800
2000
600

The above lengths needs to be cut from 4000
so 1200 + 1800 = 4000 and 1200+2000+600 =3200
so i require 2 lengths at 4000, surely its a simple excel formula, any help
would be much appreciated.
 
Not as simple as it sounds. Set up a table of lengths, inventory, formula,
and desire such as
Formula for col c copied down =MIN(B5,INT((D4/A5)))

formula for col d copied down
=D4-(A5*C5)

I can send you a workbook, if desired. Ask OFF list ONLY!!!!
Length Inv Use 5,000.00 5,000.00
2,000.00 10 2 1,000.00
4,000.00
1,800.00 10 -
-
1,600.00 0 -
-
1,400.00 10 -
-
1,200.00 0 -
-
1,000.00 0 -
-
800.00 0 -
-
600.00 10 1 400.00
600.00
400.00 2 1 -
400.00
200.00
- - -
100.00
- - -
1.00
- - -
 
Hi Dave,

What you are asking isn't simple for quite a few reasons.

First up your maths is out,
1200 + 1800 = 3000 (not 4000)
1200 + 2000 + 600 = 3800 (not 3200)

Also the cut width of your blade needs to be taken into account.
To explain the other problems I'll ignore the cut width for simplicity.

You are looking for a mathematical solution to a problem that
doesn't have a single optimal solution.

Example 1
2000 + 1400 + 600 = 4000
1800 + 1200 = 3000
This leaves a single offcut 1000 long.

Example 2
2000 + 1800 = 3800
1400 + 1200 + 600 = 3200
This leaves 2 offcuts 1x800 and 1x200.

Example 3
2000 + 1400 = 3400
1800 + 1200 + 600 = 3600
This leaves 2 offcuts, 1x600 and 1x400.

If your goal is to retain the longest single length then Ex. 1 is the best
solution.
However if you have another job that requires a 200mm length
then Ex. 2 is the best option and likewise for Ex. 3 if you happen to
need a 400mm length for something or other.

Someone may be able to come up with a way where you can mix and match
for a selection of different possibles which you can then decide which
suits your need at the time, but it will be far from simple.

HTH
Martin
 
I thought I said, Ask OFF list ONLY!!!!
Besides I can't send to
(e-mail address removed)
 
I did a re-evaluation of this question.
It appears that you have a 4000 length from which you need the requested
cuts.
If so, put your list of requested cuts in range g5:g9 and run this.
Then run again for the second series of cuts

Sub getcuts()
ml = 4000
cuts = 0
For Each c In Range("g5:g9")
If c > 0 And c < (ml - cuts) Then
cuts = cuts + c
ms = ms & "+" & c
c.ClearContents
End If
Next c
MsgBox ms
MsgBox cuts
End Sub
 
Back
Top