Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Santhosh Mani
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
John Michl
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Date & Time mully New Users to Excel 4 May 23rd 05 11:56 AM
Hyperlinks using R[1]C[1] and offset function in its cell referenc Elijah-Dadda Excel Worksheet Functions 0 March 5th 05 04:31 AM
Conversion SVC Excel Worksheet Functions 9 February 28th 05 03:29 PM
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. Robert AS Excel Worksheet Functions 4 December 2nd 04 11:49 AM
Find a Function to use accross different worksheets R. Hale Excel Worksheet Functions 3 November 25th 04 08:07 AM


All times are GMT +1. The time now is 10:03 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"