combinations

[Stephen R]

I have a finite number of values that represent length (right now I have
less than 20 values). Can Excel take these values and list all possible
combinations of the values?

Any pointers appreciated.


Stephen R.

 

[Bernie Deitrick]

Stephen,

No. There are 2,432,902,008,176,640,000 possible ways to combine 20
different values. That is, unless you have another limitation, like only
using 4 at a time....

HTH,
Bernie
MS Excel MVP

 

[Stephen R]

When I hit the x! button on my calculator, I overlooked the 18th power in
the 2. answer I received. Is there a workflow that illustrates this on a
smaller scale, say working with 5 values or so?

Thanks for your time.

Stephen R.

 

[Harald Staff]

Hi Stephen

Here's code I use a lot for permutations -which is not combinations, but
related:
http://j-walk.com/ss/excel/tips/tip46.htm

HTH. Best wishes Harald

 

[Harlan Grove]

Bernie Deitrick wrote...

No. There are 2,432,902,008,176,640,000 possible ways to combine 20
different values. That is, unless you have another limitation, like only
using 4 at a time....

....

Definitions and consistent terminology are everything!

=SUMPRODUCT(COMBIN(20,ROW(1:20)))

returns 1048575, which is 2^20-1, which is the number of different
nonempty distinct subsets of a set with cardinality 20.

Your figure is the number of PERMUTATIONS of 20 items, =PERMUT(20,20),
so ignores combinations or permutations of fewer than 20 items.

Either use the OP's terms in the standard way (combinations and
permutations mean different things) or ask for clarification.

 

[Bernie Deitrick]

Harlan,

I had thought about answering "one way", based on a strict reading of his
question (=COMBIN(20,20)), but from the OP's problem statement, I was pretty
sure he meant permutations (though I wasn't sure if he would understand the
term), so I kept it in his terms. And that is why I asked if he had some
other constraint on the problem. And he seems to have understood my reply.

Bernie
MS Excel MVP

 

[Stephen R]

Hello Bernie, Harlan,

I am looking for combinations. I mistakenly mentioned factorials
previously. My goal is to list all the different lengths available with my
set of values, including the individual lengths. Say I have one each of
lengths 1, 2 and 3. I would then have available lengths of:
1=1
2=2
3=3
1+2=3
1+3=4
1+2+3=6
2+3=5

I hope this clarifies my goal and sorry for any confusion. It is ok if the
resulting list of available lengths has duplicates. So in this example, I
can get a length of 3 two ways, use 3 or use a combination of 1+2.


Thanks for the help,

Stephen R.

 

[Stephen R]

Thanks for the link Harald.

Stephen R.

Hi Stephen

Here's code I use a lot for permutations -which is not combinations, but
related:
http://j-walk.com/ss/excel/tips/tip46.htm

HTH. Best wishes Harald

 

[Bernie Deitrick]

Stephen,

As Harlan stated, you can still have 1,048,575 possibilities with 20 values.
How many values do you actually have? Are you trying to find a combination
that adds up to a certain value? (Because that could be solved quickly with
a nifty algorithm that I can send you (but that I didn't write)).

HTH,
Bernie
MS Excel MVP

 

[Stephen R]

I have less than 20 unique values, and yes, that would be helpful to assist
at getting to a particular value or goal. That route may be easier,
especially when the resultant list will be long.


Stephen R.

 

[Bernie Deitrick]

Stephen,

Reply to this message (to me, rather than to the group), and change my
address by taking out the spaces and changing the dot to . Then I will send
you a workbook privately that will find the values that will add up to a
particular value.

HTH,
Bernie
MS Excel MVP