# Excel - Restricting a Random Query?

Asked By NolChannel on 22-May-12 11:45 PM
```I am 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.

Help?

--
NolChannel```

James Ravenswood replied to NolChannel on 23-May-12 08:18 AM
`Define "opposite"`
zvkmpw replied to NolChannel on 27-May-12 05:26 PM
```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 are not opposite "1".
The second of these are not 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 replied to zvkmpw on 27-May-12 06:46 PM
```We can do without the 3x4 table of helper constants.

Using the same logic, it is 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.```