Combination with repetitions into excel for multiple data

Nic91

New Member
Joined
Jan 10, 2016
Messages
3
Hi guys!

New tothe forum and first post for me, I tried to figure it out on my own and by looking on this forum, but not sure my mind is completely clear.

I have to say I'm quite new to this kind of statistics, so apologize if the question is actually easy!

I want to calculate the total number of possible combinations including repetitions of a set of 5 items, each one of them can assume 3 different values.

so, let's say the items are A,B,C,D and the values are 1,2,3. The combiantion may be 11111, 22222, 33333, 12222, 11222 etc.. I would like to know what is the maximum amount of this combination that can be entered and, if doable, a way to display all this combination in a list.

I tried with =COMBIN(15,3) - being 5*3 the crossing of those combinations and 3 the possible outcomes - and I get 455, which I believe it may be a low number, also, not sure if this includes repetions (ie. 11111) or not. Is it correct?

If so, how can I do to show all the possible combinations in a list?

Thanks a lot to everyone!!
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
repetitions of a set of 5 items, each one of them can assume 3 different values.

so, let's say the items are A,B,C,D and the values are 1,2,3. The combiantion may be 11111, 22222, 33333, 12222, 11222 etc.. I would like to know what is the maximum amount of this combination that can be entered and, if doable, a way to display all this combination in a list.

So no zeroes? To find all arrangements:


Excel 2010
ABCDE
111111
Sheet2
Cell Formulas
RangeFormula
A1=MOD(ROUNDUP(ROW(A1)/81,0)-1,3)+1
B1=MOD(ROUNDUP(ROW(A1)/27,0)-1,3)+1
C1=MOD(ROUNDUP(ROW(A1)/9,0)-1,3)+1
D1=MOD(ROUNDUP(ROW(A1)/3,0)-1,3)+1
E1=MOD(ROW(A1)-1,3)+1


and drag down....(3^5 = 243)

But there will be repeating numbers and repeating groups (disregarding order)

PERMUT gives you repeating numbers but nonrepeating groups (disregarding order)

COMBIN gives you nonrepeating numbers and nonrepeating groups (disregarding order)
 
Last edited:
Upvote 0
So no zeroes? To find all arrangements:

Excel 2010
ABCDE
111111

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2

Worksheet Formulas
CellFormula
A1=MOD(ROUNDUP(ROW(A1)/81,0)-1,3)+1
B1=MOD(ROUNDUP(ROW(A1)/27,0)-1,3)+1
C1=MOD(ROUNDUP(ROW(A1)/9,0)-1,3)+1
D1=MOD(ROUNDUP(ROW(A1)/3,0)-1,3)+1
E1=MOD(ROW(A1)-1,3)+1

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



and drag down....(3^5 = 243)

But there will be repeating numbers and repeating groups (disregarding order)

PERMUT gives you repeating numbers but nonrepeating groups (disregarding order)

COMBIN gives you nonrepeating numbers and nonrepeating groups (disregarding order)


thanks a lot Sheetspread - will definitely try the above!
 
Upvote 0
Cont'd:


you can find all permutations by ordering with a small() formula, then deleting duplicates


you can find all combinations with a FREQUENCY or COUNTIF formula and deleting the ones with repeating 1s,2s,3s....
 
Upvote 0
got it - thanks!

I already tried the answer you gave me above and it works perfectly, I have all the possible combinations as I wanted.

Just a question, what's the logic behind the formulas you sent me? Let's say that tomorrow I want to do the same but the numbers are, for example, 5, 10, 15, 20 - should I change the fina part with -5,15)+5 ?
 
Upvote 0
With a little change
in A1 and then copy

=MOD(ROUNDUP(ROW(B3)/(3^(COLUMN()-1)),0)-1,3)+1

Bye
 
Upvote 0
I have all the possible combinations as I wanted.

Well tuples really

Just a question, what's the logic behind the formulas you sent me? Let's say that tomorrow I want to do the same but the numbers are, for example, 5, 10, 15, 20 - should I change the fina part with -5,15)+5 ?

B__P's method works (changes the base and the power) or you can use shg's popular chinese food menu:

https://app.box.com/s/47b28f19d794b25511be
 
Upvote 0
In my post there was a typo.
Assuming you start in A1 the correct formula is

=MOD(ROUNDUP(ROW(A1)/(3^(COLUMN()-1)),0)-1,3)+1

Where 3 counts the elements you shuffle


For your last question you have to put together
=MOD(ROUNDUP(ROW(A1)/(4^(COLUMN()-1)),0)-1,4)+1
4 because 5, 10, 15, 20 are 4 elements

and

=CHOOSE(1,5,10,15,20)
where 1 is the index and the rest is the list of elements to shuffle

the result is

=CHOOSE((MOD(ROUNDUP(ROW(A1)/(4^(COLUMN()-1)),0)-1,4)+1),5,10,15,20)

Bye
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top