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.

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