Help with Combination function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Santhosh Mani - 13 Dec 2005 06:15 GMT

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.

Reply to this Message

John Michl - 14 Dec 2005 16:39 GMT

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 to this Message