Pick a random number between 1 and 3 that isn't in B2 or F2

[geewhysee]

I'm trying to do a quick excel sim of the monty hall problem. I've used RANDBETWEEN(1,3) in columns B and and F for the initial guess and the actual winning door however I need to determine which door is opened. Basically my question is how do I make excel do my title, pick a random number between 1 and 3 but if the value picked matches the value in B2 or F2 then pick again.

I tried just writing out all the possibilities but got stuck here

=if(and(B2=1,F2=1),RANDBETWEEN(2,3),if(and(b2=1,f2=2),3,if(and(b2=1,f2=3),2,if(and(b2=2,f2=1),3,if(and(b2=2,f2=2),RANDBETWEEN(...

because I want to pick randomly either the number 1 or the number 3 and I don't know of a way to make excel do that.

Any ideas?

 

[geewhysee]

Wow actually never mind I just figured it out. Add another if with randbetween(1,2)

=IF(AND(B2=1,F2=1),RANDBETWEEN(2,3),IF(AND(B2=1,F2=2),3,IF(AND(B2=1,F2=3),2,IF(AND(B2=2,F2=1),3,IF(AND(B2=2,F2=2),IF(RANDBETWEEN(1,2)=1,1,3),IF(AND(B2=2,F2=3),1,IF(AND(B2=3,F2=1),2,IF(AND(B2=3,F2=2),1,IF(AND(B2=3,F2=3),IF(RANDBETWEEN(1,2)=1,1,2),"whoops")))))))))

 

[wideboydixon]

See formula in J2:

Book1
	B	C	D	E	F	G	H	I	J
2	1				1				3
3	1				2				3
4	1				3				2
5	2				1				3
6	2				2				3
7	2				3				1
8	3				1				2
9	3				2				1
10	3				3				1
Sheet1

Cell Formulas
Range	Formula
J2	=IF(B2=F2,RANDBETWEEN(1,2)*(2-MOD(B2,2))-(B2=2)+(B2=1),VLOOKUP(B2*10+F2,{12,3;13,2;21,3;23,1;31,2;32,1},2,FALSE))

WBD

 

[Eric W]

If B2 and F2 have different random numbers between 1 and 3, and you want to get the other number, you can do it much simpler like this:

=6-B2-F2

 

[wideboydixon]

Doh! Of course!! Change my formula to:

=IF(B2=F2,RANDBETWEEN(1,2)*(2-MOD(B2,2))-(B2=2)+(B2=1),6-B2-F2)

WBD

 

[geewhysee]

The numbers can be the same. They're both just random numbers between 1 & 3 imclusive

 

[Rick Rothstein]

Here is another formula that will work...

=MID(SUBSTITUTE(SUBSTITUTE("123",B2,""),F2,""),RAND()*(2-(B2<>F2))+1,1)