View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.misc
zvkmpw zvkmpw is offline
external usenet poster
 
Posts: 153
Default Restricting a Random Query?

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.