Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Need to derive combinations for 4 elements each with 3 possible va
I have four data elements and each can have one of three possible data values
at any one point in time. How can I auto-generate in excel, the various possible data value combinations/mixes that I can get for these four items? Assume the data elements are 1, 2, 3 & 4 and that the possible values are a, b & c. Any help would be greatly appreciated. |
#2
|
|||
|
|||
Need to derive combinations for 4 elements each with 3 possible va
|
#4
|
|||
|
|||
Need to derive combinations for 4 elements each with 3 possibl
If you will forgive a slight deviation from the way you specified the
problem in your first request, the following procedure will generate all combinations of the 3 values, 0,1 and 2, in four positions: In A1, B1, C1 and D1 enter the value 2 In A2, B2, C2 and D2 enter the value 0 (zero) In A3: =IF(AND(B3=0,C3=0,D3=0),IF(A2<A$1,A2+1,0),A2) In B3: =IF(AND(C3=0,D3=0),IF(B2<B$1,B2+1,0),B2) In C3: =IF(D3=0,IF(C2<C$1,C2+1,0),C2) In D3: =IF(D2=$D$1,0,D2+1) Now drag/copy down as far as row 82. This will give you the 81 (3*3*3*3) different combinations of 0, 1, and 2 in 4 positions. You can now use these values (+1 of course) as indexes into a range containing your 3 permitted values for each position. The reason for row 1 in my solution is to generalize the solution. Row 1 contains the maximum values that can occur in each position; these values may differ one from the other. In your case they are are all 2, representing the values 0, 1, and 2. If you had larger values you would simply have dragged the formulas down further. This could easily be modified to show combinations of 1, 2, and 3, but I already had this from a prior question and chose not to change it. I hope you don't mind. HTH |
#5
|
|||
|
|||
Need to derive combinations for 4 elements each with 3 possibl
DOR,
This works brilliantly. Many thanks. LAdekoya "DOR" wrote: If you will forgive a slight deviation from the way you specified the problem in your first request, the following procedure will generate all combinations of the 3 values, 0,1 and 2, in four positions: In A1, B1, C1 and D1 enter the value 2 In A2, B2, C2 and D2 enter the value 0 (zero) In A3: =IF(AND(B3=0,C3=0,D3=0),IF(A2<A$1,A2+1,0),A2) In B3: =IF(AND(C3=0,D3=0),IF(B2<B$1,B2+1,0),B2) In C3: =IF(D3=0,IF(C2<C$1,C2+1,0),C2) In D3: =IF(D2=$D$1,0,D2+1) Now drag/copy down as far as row 82. This will give you the 81 (3*3*3*3) different combinations of 0, 1, and 2 in 4 positions. You can now use these values (+1 of course) as indexes into a range containing your 3 permitted values for each position. The reason for row 1 in my solution is to generalize the solution. Row 1 contains the maximum values that can occur in each position; these values may differ one from the other. In your case they are are all 2, representing the values 0, 1, and 2. If you had larger values you would simply have dragged the formulas down further. This could easily be modified to show combinations of 1, 2, and 3, but I already had this from a prior question and chose not to change it. I hope you don't mind. HTH |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Displaying all combinations of a range of numbers | Excel Worksheet Functions | |||
Importing XML containing Complex Elements | Excel Discussion (Misc queries) | |||
Need combinations of values from a list to add up to a specific Va | Excel Worksheet Functions | |||
triadic combinations of words | Excel Worksheet Functions | |||
Excel: Can I use Fill_Color Attribs to count elements? | Excel Worksheet Functions |