I’m going to show you how to set up this craps simulation in an Excel spreadsheet. The green line shows the results of 400 Pass bets and the blue line shows 400 pass bets with taking maximum odds, 3X-4X-5X. So starting from a blank spreadsheet, I’ll put the pass bet size here, 5. I just click there, type 5, and underneath I’ll type the labels die 1 and die 2, and underneath that I’ll put in a formula to randomly choose a die value from 1 to 6.
So I’ll say equals random left/right enter, that’s equal RAND left-right, and that generates a random number between 0 and 1.
Now if I click on the little green dot I can drag that down and make copies of that and you see that each one has the same function in it, but generates a different random number. Now if I go to this formula, it generates a random number between 0 and 1. To make it a number between 0 and 6, I’ll multiply that number by 6, and to get a whole number instead of a fraction, I’ll use the truncate function TRUNC, left and then right. That will throw away the fractional part and just have the whole number part, and that will result in a number between 0 and 5, a whole number between 0 and 5. So we want 1 through 6 we have to add a 1 to that.
So press, I type + 1 and press Enter. That’s the function to use, and then I can copy that down, and here we get a different number between 1 and 5. I can copy this function, ctrl-C, click here, control V, same function. I can drag this down. Now I’ve got two dies, two dice and the throw is just the sum of those two numbers.
Throw, say equals the cells to the left plus this cell, Enter. 5 plus 4 is 9. Similarly for the other rows, 6 plus 6 is 12, and so on.
So how do you decide whether this wins or loses? This is the pass bet so 7 or 11 wins and 2, 3, or 12 loses. 2, 3, or 12.
How do we make a function to do that? We’ll make a logical function, equals logical OR this value equals 7 comma, or that same cell equals 11. Right. OK? Let me make this column bold. I click the home and then bold.
OK. Let me copy this function down. We never got 7 or 11. Let me press f9 a few times. Now you see 11 it’s true, 7 is true, and so on. OK, it’s working as I expect.
I’m going to modify this function here. First of all I want to make this definitely column C because I’m going to copy this later and I want to always want to use exactly 2 column C. That’s our throw. Press ENTER, we should get exactly the same thing true, true, false, and so on 11 true.
OK now I want to multiply function multiply it by the value in this cell, the amount of your pass bet, that’s dollar sign A, dollar sign 1, always that exact cell A1. Press ENTER, press F9 a few times you see whenever you throw 7, you win 5, and when you throw 11, you win 5. That’s working as we expect, OK? I’m going to copy this function from this cell to this cell and modify it. Here we’re looking not for a 7-eleven but two or three or dollar sign C4 equals 12 so now we’re looking for any of these, this or this or this, 2, 3, or 12 and since this is a loss I’m going to multiply this by minus 1, times minus 1, enter OK. Copy this down, now you see whenever you throw a 2, 3, or 12 you’ve got a minus 5 in this column OK. Now what about throwing the point, what happens when you throw a point?
Throwing 4 or 10 is pretty much the same, 4 or 10. Now what happens when you throw 4 or 10? You bet, you bet 3x, that’s the maximum, that’s the maximum odds bet and if you win it pays 2 to 1, 2:1, this is just a label. I’ll make it wider so it can fit, and how do we set that up? First of all I’ll copy this cell from here to here.
This is a test for the throw being not 7 or 11 but 4 or 10, C4 equals 4 or C4 equals 10, press Enter. That means whenever you throw a 4 or 10 you should get a 5 here. Looks like it’s working with 10 here results in 5 here. Now I’m going to modify this function. What happens when you throw the point? You throw until you get a 7 or, say the point is 4; you throw until you get a 4 or 7 So I’m gonna multiply this by a logical function, if random number is less than one-third.
Why why 1/3? 1/3 if the random number is less than 1/3 because you have a 1/3 chance of winning and a 2/3 chance of losing. So if by random chance 1/3 of the time you win you get paid 7 bets. Why 7? You’ve bet three bets and it pays two to one, that’s six bets, plus you win your original pass bet, that’s seven bets.
What if you lose? You lose your 3x odds bets plus your original bet, that’s a loss of 4 so I’ll put a minus 4 there. Minus four. I put a right parenthesis, okay press Enter. OK let’s see what happens.
I’ll copy this down to all these cells now, whatever you throw a four or ten you’re going to either win 35 or lose 20 Let’s see if that works and it should, you should win one-third of the time and lose two-thirds, and if there is a 4 you lose twenty. Ten lose 20, try that a few more times you have … lose 20 win 35, OK it seems to be working now. Similarly for five or eight, five or eight, the odds bet is 4X, 4X, and the payoff if you win is 3:2 so I’m going to copy this function, paste here, modify it for this point, five.
I made a mistake, there’s an it should be a nine there, five or nine um let’s see instead of one-third the chance of winning is two-fifths, two-fifths, if you win it’s seven because you’re betting 4X and paste 3 to 2 that’s 6X plus your original bet is 7; if you lose you lose 4x plus your original bet, a pass bet, that’s minus 5 OK let me correct this label 4 or 10, 5 or 9, okay let me check this again: if C4 equals 5 or 9 and if random is less than 2/5, you win 7 bets or lose 5 bets. It seems right, copy. Now we’re looking for throwing 5 or 9 over here, here 5 is thrown and it loses 25 and here 5 thrown again it wins 35; seems to be working right; throw a 9 minus 25, 5 minus 25 okay Same thing for this column okay? Then if a 6 or 8 point is thrown, six or eight, the odds bet is, maximum odds bet is 5x and the payoff on the win, on the payoff ratio is 6:5. Let’s copy this function ctrl-C click ctrl-V, modify We’re now looking for six or eight, the odds of winning is five elevenths, 5/11; if you win, you win six to five on your 5x which is six bets plus your original bet, that’s seven bets; if you lose, you lose your 5x bets plus your original pass bet, that’s a minus six There’s the formula, press ENTER. Let’s copy this down now, when you throw a six or an eight, you should either lose 30 dollars or win 35, let’s try that a few times: yes six or eight yes, seems to be working right.
Then what is your win? It’s simply the sum of these columns here, only one of these columns will have a number; the rest will be all zeros, so say equals this cell plus this cell plus this cell plus this cell plus this cell, Enter. So this cell is E4 F4 G4 H4 I4, the sum of these columns, let’s drag this down so whatever number is active in this column gets in here; the zeros are ignored.
Now what is your running total or your bankroll? Let’s start with a bankroll of zero, enter in this cell and your bankroll is simply your previous bankroll plus the result of the current bet, enter. So I just cell above plus the cell to the left, so as you win or lose, you lose 30 you win 35, you’re at five you lose 30 and minus 25, you win five it’s minus 20 and so on. That’s pretty much the whole simulation. Here’s your final result after ten bets. You can press F9 and see how far behind you are after 10 bets.
In the next video I’ll expand it to 400 bets and graph it and also show the results of just the pass bet without taking odds.