First, columns A and B use
RAND(), a random number generator, to randomly set door 1, 2, or 3 as the one with the prize and the one you choose at first.
RAND() creates a number between 0 and 1 with a multiple digits after the decimal point, so we have to do two things to it to get what we want. First, it's multiplied by 3 so the range of possible numbers it might give us is from 0 to 3 instead of 0 to 1:
=RAND()*3
Then we need that rounded up to the nearest integer. There are
ROUND(),
ROUNDUP(), and
ROUNDDOWN() functions, but
ROUND() would give us 0, 1, 2, or 3, and
ROUNDDOWN() would give us 0, 1, or 2. All three of these need two "arguments" inside the parentheses, separated by a semicolon in OpenOffice: first, what we want rounded off, and second, how many digits after the decimal point we want it rounded off to, which in this case is zero. So we tuck the thing we want rounded off,
RAND()*3 from above, into it like this:
=ROUNDUP(RAND()*3;0)
Since both columns A and B are random, sometimes they'll match and sometimes they won't, and which one of those is the case will determine Monty's next action. So we'll need separate columns to handle each situation. (Or we could stuff it all into one giant multi-layered if-then formula with if-thens inside of other if-thens, but breaking it down like this keeps things easier to see while editing.)
The simpler case is if you didn't already choose the winning door, so we'll do that first, in column C. Monty has to open the only door that's left, the one you didn't choose that doesn't have the prize. What I have in the table above is an
IF() formula, and those need three arguments in the parentheses: the condition that determines what's going to happen, then the output if it's true, then the output if it's false. In this case, the part we're really after is the output if A and B don't match, which I've made red below. It's a simple math formula using "A2" and "B2" to refer to the cells in columns A and B on row 2 (the two door numbers we've already randomly chosen), subtracting them from 6, because all three door numbers, 1+2+3, add up to 6, so whenever two are already indicated then 6 minus them indicates the other one. Another option for output from an
IF() formula, which I've made blue below, is to use quotation marks to just have it write any text you want in one case or the other; you could have the cell say
"I'm going to DisneyWorld" if A2=B2 and
"Death to the opposition" if they're not equal. But if you put nothing at all in the quotation marks, the cell will look blank in one case or the other, which sometimes makes certain spreadsheets easier to read:
=IF(A2=B2;"";6-A2-B2)
If I didn't care about making the spreadsheet look neat and clean, I could have just used the red part above, and then it would give a visible result in all cases, whether the player had originally picked the right door or not, even though this column is really only for when (s)he didn't. We'll use
IF() to exclude output we're not using in columns D, E, H, and I as well.
If the player picks the right door on the first try, Monty has to choose from the other two, so we need a mathematical representation of a random choice equivalent to a coin flip even though Monty doesn't flip a coin. For this, we need to go back to
RAND(), but this time, the math we're applying to it causes the output to always be either 1 or -1: first, rounding off to 0 or 1, then doubling that so 0 stays 0 and 1 becomes 2, then subtracting 1:
=ROUND(RAND();0)*2-1
We'll use that difference in a later formula in another cell, so that cell will end up using 1 half of the time and -1 the other half of the time. Meanwhile, to complete the coin-flip formula, we'll tuck it into an
IF() to eliminate output we won't use, since Monty's choice of two doors doesn't apply when the player doesn't choose the right door the first time:
=IF(A2=B2;ROUND(RAND();0)*2-1;"")
Next, Monty's actual choice of door. This is in another of those standard
IF()s that we're using here to keep the cell clear when it's not applicable, so we only need to watch the details of one of the three arguments in the parentheses, which only has a few possible inputs & outputs. The door with the prize is the door the player chose. If that's #1, we need the output to say 2 or 3; if it's #2, we need the output to say 1 or 3; and if it's #3, we need 1 or 2. That means...
if starting at door "1" in cell A2:
2.5±0.5
if starting at door "2" in cell A2:
2±1
if starting at door "3" in cell A2:
1.5±0.5
You can get individual custom outputs from specific inputs like this by putting multiple
RAND()s inside
IF()s inside more
IF()s, but it's structurally simpler to find a mathematical formula that yields the only kinds of output you need in all of the few possible input cases it could get (in this case, randomly using multiplication by either 1 or -1 from column D to take care of the "±", making Monty's choice between two doors to open random like a coin flip):
=(6-A2)/2+D2*((2-(2-A2)^2)/2)
Then it all fits into one
IF() which refers back to only one
RAND() in column D:
=IF(A2=B2;(6-A2)/2+D2*((2-(2-A2)^2)/2);"")
After that the formulas get simpler.

In column F, we just display the result from either column C or column E, whichever is applicable, so we can have them all in the same place:
=IF(A2=B2;E2;C2)
(We could have had a single formula incorporating everything in C and E all along instead of taking three columns, but it would have looked like this...)
=IF(A2=B2; (6-A2)/2+D2*((2-(2-A2)^2)/2) ; 6-A2-B2 )
Now that we know not only which one the player chose but also which one Monty opened, that only leaves one other door for column G, the one the player has the option of switching to, using the same simple little mathematical stunt as in column C:
=6-B2-F2
And now we can find which door the player will find out is the one with the prize: the player's original choice, or the one (s)he could now switch to, in columns H and I:
=IF(A2=B2;1;"")
=IF(A2=G2;1;"")
That will put a "1" in whichever column would be the winning move in this case, and leave the other one empty. If you wanted numbers in ever cell of these columns, you could use a 0 instead of the quotation marks, and the
SUM() formulas I mentioned earlier would still have the same results.