Combinations of options on the table

[es1]

Hello All

I am a first time user and I really hope someone can help me.

My issue is have to create a table which lists all the combinations from the different criteria.

There are six criteria, 4 criteria only have two options (0 or 1), but there are two criteria that have 3 options (0, 1, 2).

I think the total number of possibilities is 144.

Does someone have an easier way to come up with the 144 options or can help me complete this table?

See attached.

If someone can please help, that would be much appreciated.

Many thanks,
ES

[Pete_UK]

I'm not sure what your lower table is meant to represent. Do you want a list of all those variations, i.e. 000000, 000001, 000002, 000010, 000011, 000012 ... and so on, where each digit represents one criterion?

Pete

[es1]

Hi Pete, I am not sure if you got my last message.

I was saying that you are on the right path, I have attached what I have done so far, it's all on trail and error.

Thank you again for replying :-)

[Pete_UK]

I've changed the top part of Sheet1 to make it a bit more flexible, allowing you to list up to 5 options for each criteria. Then, working from criteria 6 (as the least significant digit of the final result), I put this formula in J11:

=INDEX(J$3:J$7,MOD(ROWS($1:1)-1,J$2)+1)

When this formula is copied down it produces the list of options and then repeats them, i.e. 0, 1, 2, 0, 1, 2, 0, 1 ... and so on.

The formula in I11 is slightly different, i.e.:

=INDEX(I$3:I$7,MOD(INT((ROWS($1:1)-1)/PRODUCT(J$2:$J$2)),I$2)+1)

When this is copied down it repeats the first element for criteria 5 for as many options as there are in criteria 6 (i.e. 3 zeros) and then repeats the second element the same number of times, and does this for as many elements as there are in criteria 5, and then repeats the whole sequence again, so you get 0 0 0 1 1 1 0 0 0 1 ... and so on.

The formula from I11 can be copied into H11, and it becomes:

=INDEX(H$3:H$7,MOD(INT((ROWS($1:1)-1)/PRODUCT(I$2:$J$2)),H$2)+1)

The main change is with the range in the PRODUCT term (there are also changes from column I to column H), so this now takes account of the fact that there are 2 options in column I and 3 options in column J, so the sequence of criteria 4 options will be repeated six times, i.e. 0 0 0 0 0 0 1 1 1 1 1 1 0 0 0 ... etc. as the formula is copied down.

The same formula in H11 can be copied into G11, F11 and E11, and when copied down the appropriate sequences for those criteria are set up. As there are 144 unique combinations, the formulae in row 11 need to be copied down to row 154 - any further, and the pattern will repeat from the beginning.

Now, you can put whatever you want as the options for each criteria (e.g. "Y" or "N", "TRUE" or "FALSE", or letters), and you can have up to 5 options for each criteria, and the formulae will automatically adjust - you may need to copy the formulae down further if you use more options.

Hope this helps.

Pete

[newdoverman]

I like Pete's solution. Here is a "brute force" method progressing from left to right and then entering the values obtained into the table.