combinations

  • Thread starter Thread starter Stephen R
  • Start date Start date
S

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.
 
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
 
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.
 
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....
Click to expand...
....

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.
 
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
 
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,

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
 
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.
 
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
 
Back
Top