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!!
 

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,112
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:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">A1</th><td style="text-align:left">=MOD(<font color="Blue">ROUNDUP(<font color="Red">ROW(<font color="Green">A1</font>)/81,0</font>)-1,3</font>)+1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B1</th><td style="text-align:left">=MOD(<font color="Blue">ROUNDUP(<font color="Red">ROW(<font color="Green">A1</font>)/27,0</font>)-1,3</font>)+1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C1</th><td style="text-align:left">=MOD(<font color="Blue">ROUNDUP(<font color="Red">ROW(<font color="Green">A1</font>)/9,0</font>)-1,3</font>)+1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D1</th><td style="text-align:left">=MOD(<font color="Blue">ROUNDUP(<font color="Red">ROW(<font color="Green">A1</font>)/3,0</font>)-1,3</font>)+1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E1</th><td style="text-align:left">=MOD(<font color="Blue">ROW(<font color="Red">A1</font>)-1,3</font>)+1</td></tr></tbody></table></td></tr></table><br />

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:

Nic91

New Member
Joined
Jan 10, 2016
Messages
3
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!
 

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,112
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....
 

Nic91

New Member
Joined
Jan 10, 2016
Messages
3
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 ?
 

B___P

Active Member
Joined
Oct 31, 2015
Messages
415
With a little change
in A1 and then copy

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

Bye
 

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,112
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
 

B___P

Active Member
Joined
Oct 31, 2015
Messages
415
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
 

Forum statistics

Threads
1,082,360
Messages
5,364,920
Members
400,815
Latest member
Joaquin Phoenix

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top