|
excel formula to create a cutting list help needed please
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. |
excel formula to create a cutting list help needed please
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 - - - -- Don Guillett Microsoft MVP Excel SalesAid Software "davers" wrote in message ... 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. |
excel formula to create a cutting list help needed please
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 "davers" wrote in message ... 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. |
excel formula to create a cutting list help needed please
Thanks a lot a a workbook would be great.
cheers "Don Guillett" wrote: 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 - - - -- Don Guillett Microsoft MVP Excel SalesAid Software "davers" wrote in message ... 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. |
excel formula to create a cutting list help needed please
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 -- Don Guillett Microsoft MVP Excel SalesAid Software "davers" wrote in message ... 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. |
| All times are GMT +1. The time now is 12:42 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com