Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Combination function
I want to list down all possible combinations of sizes from a size list. We
produce Paper Rolls in a specified size ie; 161 inch. We receive cusomer orders in small sizes. So I want to know which sizes could be combined to slice the Big roll to avoid maximum loss. For eg I have orders for 8 sizes, say 31, 35, 39, 41, 45, 55, 57, 59 inches. If I could calculate the combination of these 8 sizes with 1 to 8 items (with the formula - =COMBIN(8,1)+=COMBIN(8,2)+=COMBIN(8,3)+ =COMBIN(8,4) so on) I am getting a result of total 255 combinations. How can I get all these 255 combinations list down in different row? If I get that result, I can filter the combinations which match beween 159 and 161 inches. I want a flexible solution where number of sizes could be varied all the time, here in this example 8 sizes with combination of 1 to 8 items, so the formula should be flexible to calculate n sizes with combination of 1 to n items. I hope my question is clear and it will be very helpful if one could help me with this. Thanks in advance for your valued solution. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Combination function
If I understand what you are trying to do, you may be better off using
the Solver tool. Arrange your data like this... COLA COLB COLC COLD Lengths Include Qty Total 31 1 0 0 35 1 0 0 39 1 0 0 41 1 0 0 45 1 0 0 57 1 0 0 55 1 0 0 59 1 0 0 Total from Roll 0 Roll Length 161 Variance 161 D2 = A2*B2*C2 and copy it down to D9 D10 = SUM(D2:D10) D11 = 161 (or whatever the row length is) D12 = D11 - D10 The include column is added to allow you to temporary include or not include certain lengths. If you want to include a length as a possibility, enter 1. Enter 0 to temporarily not include it. Entering a 1 does not guarantee it will be used but Solver will at least consider it. Call up Solver via Tools Solver. Set Target Cell = D12 Equal to MIN By Changing Cells C2:C9 Subject to the constraints C2:C9 = Int C2:C9 = 0 D12 = 0 Then Click solve and it will indicate the combination that minimizes waste. After that set, you can change the "Include" value to 0 for items in the original solution. Then run solver again and it will find the next best set. You could get fancier by adding a column that would indicate the quantity needed and an additional constraint so that solver wouldn't suggest 4 items of a length when you only need two. This should give you some ideas on getting started. - John www.JohnMichl.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date & Time | New Users to Excel | |||
Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions | |||
Find a Function to use accross different worksheets | Excel Worksheet Functions |