Restricting a Random Query?

[NolChannel]

I'm having a little trouble with the random function.

In the first line, I want a random integer between 1 through 4, eas
enough.

In the second line, and every line thereafter, I want a random integer
through 4... however, I do not want an 'opposing number'. I can roll
twice in a row, but if I roll '1' I cannot roll '2', and vice versa.
and 4 are also opposites.

Help

 

[James Ravenswood]

Define "opposite"

 

[zvkmpw]

I'm having a little trouble with the random function.

In the first line, I want a random integer between 1 through 4, easy
enough.

In the second line, and every line thereafter, I want a random integer 1
through 4... however, I do not want an 'opposing number'. I can roll 1
twice in a row, but if I roll '1' I cannot roll '2', and vice versa. 3
and 4 are also opposites.

I put these constants in B1:E3 to help:
1 2 1 1
3 3 2 2
4 4 3 4
The first of these columns has the acceptable values that aren't opposite "1".
The second of these aren't opposite "2", etc.

In A1, I put
=RANDBETWEEN(1,4)

In A2, I put
=OFFSET(A$1,RANDBETWEEN(0,2),A1)
and copied this formula down column A. This chooses an acceptable value at random from the correct column as determined by the preceding value.

It seems to work, if I understand the problem.

Hope this helps.

 

[zvkmpw]

I put these constants in B1:E3 to help:

1 2 1 1
3 3 2 2
4 4 3 4
The first of these columns has the acceptable values that aren't opposite "1".
The second of these aren't opposite "2", etc.

In A1, I put
=RANDBETWEEN(1,4)

In A2, I put
=OFFSET(A$1,RANDBETWEEN(0,2),A1)
and copied this formula down column A.

We can do without the 3x4 table of helper constants.

Using the same logic, it's enough to put in A1:
=RANDBETWEEN(1,4)
then put in B1
=CHOOSE(3*A1-RANDBETWEEN(0,2),1,3,4,2,3,4,1,2,3,1,2,4)
and copy down.