Combinations

[goggem]

I want a formula to work out every 9-digit number, that uses only the values 1,2,3 and 4.

(I don't mind having the numbers in separate cells)

Like this:

1 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 2
1 1 1 1 1 1 1 1 3
1 1 1 1 1 1 1 1 4
1 1 1 1 1 1 1 2 1
1 1 1 1 1 1 1 2 2
1 1 1 1 1 1 1 2 3
1 1 1 1 1 1 1 2 4
1 1 1 1 1 1 1 3 1
1 1 1 1 1 1 1 3 2
1 1 1 1 1 1 1 3 3
1 1 1 1 1 1 1 3 4
1 1 1 1 1 1 1 4 1
1 1 1 1 1 1 1 4 2
1 1 1 1 1 1 1 4 3
1 1 1 1 1 1 1 4 4
1 1 1 1 1 1 2 1 1
1 1 1 1 1 1 2 1 2
1 1 1 1 1 1 2 1 3
1 1 1 1 1 1 2 1 4
1 1 1 1 1 1 2 2 1
1 1 1 1 1 1 2 2 2
1 1 1 1 1 1 2 2 3
1 1 1 1 1 1 2 2 4
1 1 1 1 1 1 2 3 1
1 1 1 1 1 1 2 3 2
1 1 1 1 1 1 2 3 3
1 1 1 1 1 1 2 3 4
1 1 1 1 1 1 2 4 1
1 1 1 1 1 1 2 4 2
1 1 1 1 1 1 2 4 3
1 1 1 1 1 1 2 4 4
....
....


I can do the first column with a formula - see here: http://www.editgrid.com/user/adamhman/Combinations

I would like to be able to do the other columns in a similar way.

Any help would be appreciated!

 

[Andrew Fergus]

Hi

Use a separate column for each digit. In your first row enter the value 1 into each column. In cell I2 (the 9th column) use the formula:
=MOD(I1,4)+1
and copy down,

In cell H2 (the 8th column) use the formula:
=IF(AND(I2=1,I1=4),MOD(H1,4)+1,H1)
and copy down 255 rows.

Copy the formula from cell H2 into cells A2:G2 and copy down 255 rows.

HTH, Andrew

 

[jbeaucaire]

There are 94 million + answers, according to the macro I tried to use....if that's true you're not going to get that on Excel 2003.

There's a macro on this website that will give you what you want, perhaps Excel 2007 can run it with your desired parameters:
http://www.ozgrid.com/forum/showthread.php?p=148992

Put a C in A1, 9 in A2, the number 1 in the next 9 cells, the number 2 in the next 9, 3 in the next 9 and 4 in the final 9. Then run the macro.

The answers will appear in a comma delimited list. You can parse those out to separate cells if you wish, or run another macro to remove the commas.

 

[Andrew Fergus]

My bad with the 255 rows - I only tested it for the first 4 digits!

The first 65536 rows gets me to the number:
144444444
which is exactly a quarter of the numbers needed so I suspect the total number will be a lot less than 94 million!

Andrew

 

[mikerickson]

Put this CSE formula in a cell and drag down

=SUM((10^(COLUMN($A$1:$I$1)-1))*(1+MOD(INT((ROW(A1)-1)/(4^(COLUMN($A$1:$I$1)-1))),4)))

As has been noted, thats a lot of values.

What you are doing is counting from 0 to 4^10 (and then substituting 1 for 0, 2 for 1, 3 for 2, 4 for 3)

What do you need all these numbers for?
Analysis may get you your result without listing a bunch of numbers.

 

[goggem]

I came up with an easier way to do it that is not recursive based on the above values.
its as lease recursive as possible. all values are based off the number, from 0-262,143
(since there are 262,144 combinations)

http://www.editgrid.com/user/adamhman/Combinations

Just thought I'd share.

 

[Andrew Fergus]

That seems an awfully complicated way of doing it! But hey if it works then so be it.....

Cheers
Andrew