table formula

[jeel]

I would like to create a table that is 16 rows deep and 16 columns across
where the
numbers 1:16 are in the cells in column a. Then going across I need each
number to appear in each row without the number being repeated in the column.
Similar to suduko but using 16 spaces. Is there a formula to do that?

 

[Bob Phillips]

Here is one way.

First clear the numbers in A1:A16,

First, ensure cell A1 is empty and goto Tools>Options and on the Calculation
tab check the Iteration checkbox to stop the Circular Reference message.

Next, type this formula into cell B1
=IF(($A1="")+(AND(B1>0,COUNTIF($B1:$Q1,B1)=1)),B1,INT(RAND()*16+1))
it should show a 0

Copy B1 across to Q1.
Copy B1:Q1 down to B16:Q16.

Finally, put some the values 1-16 in A1:A16, and all the random numbers
will be generated, and they won't change.

To force a re-calculation, clear cell A1:A16, edit cell B1, don't change it,
just edit to reset to 0, copy B1 across to Q1 and B1:Q1 down to B16:Q16then
, and re-input A1:A16.


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[jeel]

Thank you for your help. The formula worked well until I got to the part about
re-calculation. I got to the edit mode but I couldn't figure out how to get
it to reset.
I am using Excel 2007 with Vista. Also, I more question please. I copied
your note and pasted it into an Excel worksheet. The actual formula won't
copy. I copied just the formula without the equal sign. That worked. This
happens to me every time I
try to copy a formula from the answers. Is there a setting or something I
need to change? Thank you, jeel

 

[Bob Phillips]

I don't know how else to say it, it seems quite clear to me

To force a re-calculation,
- clear cell A1:A16, - just delete the content in those cells
- edit cell B1, don't change it, just edit to reset to 0, - in other words,
F2 then hit return to force it back
- copy B1 across to Q1 and B1:Q1 down to B16:Q16, - self-explanatory
- and re-input A1:A16. - re-enter 1 to 16 in these cells

No idea on the other point I am afraid.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[jeel]

Thank you for the further instructions. Your formula worked perfect.
Unfortunately I
did not explain myself clearly. I need the table to be just like the one you
showed me except that when I put the number 1 in cell a1, I do not want it to
appear in row
1 again, etc down to 16. That would mean that I would only need 15 rows
across.
I am sorry for not being clearer. Thank you for your time. jeel

 

[T. Valko]

numbers 1:16 are in the cells in column a.

Are the numbers in column A (1 to 16) in sequential order or are they in
random order?

So, you want a 16 x 16 matrix with no duplicates on any row or in any
column?

 

[jeel]

The numbers 1:16 in column a could be in any order. Otherwise your assumption
is correct. Thank you jeel

 

[T. Valko]

Will you be manually entering the values in column A?

 

[T. Valko]

Try this:

A2:A17 = numbers 1 to 16 in any order

Enter the numbers 1 to 16 in the range R2:R17
Enter this formula in S2 and copy down to S17:

=RAND()

Select the range R2:S17 and sort on column S (any order, ascending or
descending, doesn't matter)

Enter this formula in B2 and copy across to P2:

=IF($A2="","",INDEX($R$2:$R$17,MATCH(A2,$R$2:$R$17,0)+1-(MATCH(A2,$R$2:$R$17,0)=16)*16))

Select the range of formulas in B22 and copy down to B1717

To get a new "shuffle" either enter the numbers in column A again in a
different order or resort R2:S17.

Each row will contain the numbers 1 to 16 with no repeats and each column
will contain the numbers 1 to 16 with no repeats.

 

[jeel]

Your formula works perfectly. Thank you for your help and time. jeel
I am working on another similar formula. It goes like this.
Column a, is the numbers 1:16. Column b is again 1:16 but in a different order
all the way across like the other matrix but if cell a1 is 10 then cell a10
needs to be 1, if cell a2 is 16 then cell 16a should be 2,etc to finish
column a and then the rest of the columns would work the same way but so that
no number is repeated in each row. Each column would need to be referenced
back to column a. For example, if cell a1 is 1, cell b1 is 10 then cell b10
would need to be 1.
Thank you for any help you can give me. jeel

 

[T. Valko]

I'm lost! I'm not following you at all on this new setup.

Can you post a small example using only a 5 x 5 matrix with the numbers 1 to
5 and explaining how they end where they are?

 

[jeel]

This has to be done with an even numbers. Column a includes the base numbers.
It would have 16 rows, with the numbers 1:16, in any order. There should
also be 16 columns with the number 1:16 in them. Again randomly, but each
number can appear only once in each row. And once in each column. In trying
to solve this I tried to think of it 2 columns at a time. Column a is always
the first column and the
other column would be each of the others, one at a time. So if cell a1 is 1
and cell b1 is 6 then cell a6 is 6 and cell b6 is 1, etc according to the
example below.
I think a person could solve this manually but it would be easier if someone
could figure out a formula. I can't seem to. Your other formula worked well.
Thank you for your time and effort. jeel
Cell
a1...1 b1...6 c1...5 d1...4 e1...3 f1...2
a2...2 b2...5 c2...4 d2...3 e2...6 f2...1
a3...3 b3...4 c3...6 d3...2 e3...1 f3...5
a4...4 b4...3 c4...2 d4...1 e4...5 f4...6
a5...5 b5...2 c5...1 d5...6 e5...4 f5...3
a6...6 b6...1 c6...3 d6...5 e6...2 f6...4

 

[T. Valko]

Ok, I think I understand the logic of the first 2 columns. However, in order
to do that with formulas you would have to manually enter the first half of
the values. For example, you'd manually enter the numbers in A1:B3 then
A4:B6 would be formulas. But then I don't see how this pattern can be
maintained in the remaining columns without producing duplicates.

 

[Dana DeLouis]

=IF($A2="","",INDEX($R$2:$R$17,MATCH(A2,$R$2:$R$17,0)+1-(MATCH(A2,$R$2:$R$17,0)=16)*16))

Don't know if this would help. If the range R2:R17 has the name "X" them
perhaps.

=INDEX(X,MOD(MATCH(A2,X,0),16)+1)

The idea being that Mod(n-1,y)+1 stays within 1-y
All we are doing here is just shifting the next column up by 1.
Since the op just wants to shift it by 1, another option might be:

B2: =A3
B3: =A4
etc...
B17: =A2

if cell a1 is 10 then cell a10
needs to be 1, if cell a2 is 16 then cell 16a should be 2,etc to finish
column a

What you are describing here in Combination theory is an Inverse
Permutation.
(basically the first item points to 1, the second points to 2, etc.)
A permutation by its inverse returns the Identity, which is 1,2,3...n
I don't see how this will do what you want here. I copied your other
example using 6 *6 below:

There are 720 Permutations of (1-6), of which 76 are their own Inverse.
Each one of your columns "IS" it's own inverse! I don't see how this could
help.

a1...1 b1...6 c1...5 d1...4 e1...3 f1...2
a2...2 b2...5 c2...4 d2...3 e2...6 f2...1
a3...3 b3...4 c3...6 d3...2 e3...1 f3...5
a4...4 b4...3 c4...2 d4...1 e4...5 f4...6
a5...5 b5...2 c5...1 d5...6 e5...4 f5...3
a6...6 b6...1 c6...3 d6...5 e6...2 f6...4

 

[jeel]

Thank you for your time.
http://www.freefilehosting.net/download/3c17b
This is a link to a file showing what I need to accomplish.
I don't see what cell I should put your formula in. I tried several
and got back various error messages. I saved this file in Excel 97-2003
although I am working with Excel 2007 and Vista. In some things this
makes a difference. Thank you. jeel

 

[Dana DeLouis]

I believe that there are a large number of possible solutions to your "Latin
Square" problem.

http://en.wikipedia.org/wiki/Latin_square

 

[T. Valko]

MOD(MATCH(A2,X,0),16)+1

I knew there was probably a more elegant way of doing:

MATCH(A2,$R$2:$R$17,0)+1-(MATCH(A2,$R$2:$R$17,0)=16)*16)

To the OP, I'm out of suggestions on your new setup.

 

[jeel]

To T. Valko...I don't understand what you are suggesting I do with the
formulas you provided. What cell should I put them in. I tried them in
various cells and received various error messages. Thank you to everyone who
took a look at my problem. I went to Wikpedia and read about Latin squares.
It says that they are similar to Sudoku puzzles so I will take a look at
solving my problem with some of those formulas. Thank you for your time and
effort. jeel

 

[Dana DeLouis]

Hi. Your rule didn't make sense to me. If you are just looking for a
second example of size 16, here's one of many possible solutions.
A Multiplication Table of a Permutation Group is a Latin Square.
Don't know how to do this in Excel, so here's a quick Math program. We take
a Cyclic Group (Rotated by 1), and then take the reverses. (ie..A Dihedral
Group)

dg = DihedralGroup[16/2]

{1,2,3,4,5,6,7,8},
{8,1,2,3,4,5,6,7},
{7,8,1,2,3,4,5,6},
{6,7,8,1,2,3,4,5},
{5,6,7,8,1,2,3,4},
{4,5,6,7,8,1,2,3},
{3,4,5,6,7,8,1,2},
{2,3,4,5,6,7,8,1},
{8,7,6,5,4,3,2,1},
{7,6,5,4,3,2,1,8},
{6,5,4,3,2,1,8,7},
{5,4,3,2,1,8,7,6},
{4,3,2,1,8,7,6,5},
{3,2,1,8,7,6,5,4},
{2,1,8,7,6,5,4,3},
{1,8,7,6,5,4,3,2}

So, one way...

MultiplicationTable[dg, Permute]


{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16},
{2,3,4,5,6,7,8,1,10,11,12,13,14,15,16,9},
{3,4,5,6,7,8,1,2,11,12,13,14,15,16,9,10},
{4,5,6,7,8,1,2,3,12,13,14,15,16,9,10,11},
{5,6,7,8,1,2,3,4,13,14,15,16,9,10,11,12},
{6,7,8,1,2,3,4,5,14,15,16,9,10,11,12,13},
{7,8,1,2,3,4,5,6,15,16,9,10,11,12,13,14},
{8,1,2,3,4,5,6,7,16,9,10,11,12,13,14,15},
{9,16,15,14,13,12,11,10,1,8,7,6,5,4,3,2},
{10,9,16,15,14,13,12,11,2,1,8,7,6,5,4,3},
{11,10,9,16,15,14,13,12,3,2,1,8,7,6,5,4},
{12,11,10,9,16,15,14,13,4,3,2,1,8,7,6,5},
{13,12,11,10,9,16,15,14,5,4,3,2,1,8,7,6},
{14,13,12,11,10,9,16,15,6,5,4,3,2,1,8,7},
{15,14,13,12,11,10,9,16,7,6,5,4,3,2,1,8},
{16,15,14,13,12,11,10,9,8,7,6,5,4,3,2,1}

Again, doesn't really help w/ Excel, but maybe something to get you going.

Interesting is that if we Shuffle our group, we can get a random looking
Latin Square.

dg=DihedralGroup[16/2]//RandomSample;
MultiplicationTable[dg,Permute]

{13,4,5,2,3,9,12,11,6,16,8,7,1,15,14,10},
{4,13,12,1,7,15,5,10,14,8,16,3,2,9,6,11},
{11,10,13,7,15,8,4,6,12,2,1,9,3,16,5,14},
{2,1,7,13,12,14,3,16,15,11,10,5,4,6,9,8},
{8,16,1,12,14,11,2,9,7,4,13,6,5,10,3,15},
{15,9,16,14,11,4,8,3,1,5,12,10,6,13,2,7},
{10,11,4,3,9,16,13,14,5,1,2,15,7,8,12,6},
{5,12,14,16,1,7,6,13,11,15,9,2,8,3,10,4},
{14,6,10,15,8,2,11,5,13,3,7,16,9,1,4,12},
{7,3,9,11,4,5,15,2,16,6,14,13,10,12,8,1},
{3,7,15,10,13,12,9,1,8,14,6,4,11,5,16,2},
{16,8,2,5,6,10,1,15,3,13,4,14,12,11,7,9},
{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16},
{9,15,8,6,10,13,16,7,2,12,5,11,14,4,1,3},
{6,14,11,9,16,1,10,12,4,7,3,8,15,2,13,5},
{12,5,6,8,2,3,14,4,10,9,15,1,16,7,11,13}


Anyway, interesting subject. :>)

 

[jeel]

The 3rd example seems to be what I want. Could you give me some further
explaination as to how you did it? Would it work with any number? Or would it
have to be even numbers. I am not very computer literate so simplify,
simplify, simplify.
Thank you for your help. jeel