Number combinations for a specific value

[raineysky]

I am trying to figure out how to calculate a "Charge" for a lumber mill. The lumber is in various lengths from 8-24 feet long and needs to equal 96 feet total length to fit (98 absolute max) in the drying kiln. The other variable is the number of "stacks" of each size. There needs to be at least 6 "stacks" of lumber of the same length or another length must be used in order to equal 96 feet. Inventory is done every morning. Thank You very much for any help. Example:


2x4's


8' 6 stacks

10' 12 stacks

12' 0 stacks

14' 16 stacks

16' 6 stacks

on up to 24'

 

[Hui]

Rainesky


There will be a number of solutions to your question, do you have more constraints like supply or sales constraints?


You could make up a large table of Lengths and Multiples of to arrive at the 96' result


If you have enough constraints you could try Solver


Solver will automatically go through a number of scenarios and look to maximimise, minimise etc results based on dependancies that you setup.


Solver is not installed in Excel 2007 by Default

Goto Excel Options, Addins Select "Manage Excel Addins" and Check Solver

The Solver Tab will now show up on the Data Tab


Solver allows you to goal seek a result (in your case =96') subject to applying a number of constraints.

In your case and not knowing what A, B, C & D are, but a Constraint maybe

A + B + C + D >= 96

A + B + C + D <= 98


A>0, B>0, C>0, D>0

A,B,C,D < 24

A,B,C & D must be integers

etc


When applying constraints and running solutions remember that excel may find solutions you don't expect eg: B = -10, This means that you need to add adequate constraints to ensure you get the answers you want ie: A>0 & B>0 & C>0 & D>0

 

[raineysky]

Thank You Hui for your response. I will have to play around with Solver a bit because I have never used it. There really is no more constraints except that the mill cuts 2x4, 2x6, 2x8, 2x10, and 2x12's lumber in lengths of 8-10-12-14-16-18-20-22-24 feet. A "Charge" must be 96' in length and contain only one width of lumber such as 2x6's, etc...One day they will dry 2x6's and the next day they may dry 2x12's just depending on how many "stacks" of lumber is in the yard inventory.


My goal is to have an excel spreadsheet where the daily inventory numbers can be entered and it will automatically do the calculations. This place is so out of date they only have Excel 2000.