Spreadsheet help (OpenOffice)

[quixotecoyote]

I've written a spreadsheet in OpenOffice that lists the possible results of rolling 2 six sided dice after adding any number.
roll
It also lists the possible results of a roll of another set of 2 six sided dice after adding any number.

The numbers added need not be the same, and usually aren't.

It then lists all possible results after subtracting the first set of results from the second.

If both dice rolls had "0" added to them it would list all integers from 10 to -10.

If the first dice roll had "1" added to it and the second had "0" added to it, it would list all integers from 11 to -9.

It also lists the probability for each of the possible values gained in that way.

I am trying to write a formula to determine the chance of the first dice roll being equal to or greater than the second.

I know that to find that for any given roll, I would sum the probabilities starting from the probability associated with the difference of 0 to the probability assoiciated with the largest positive difference.

http://i49.photobucket.com/albums/f253/quixotecoyote/spreasdsheet.png
eta: (The roll 2 modifer is actually 4. It was a glitch in forumula flow. Everything else is ok so I'm not going to upload another pic)

I can manually set it to sum probabilities to make it tell me the probability of roll 1 meeting or beating roll 2 is just under 30%, but because the 0 value keeps moving with every new set of values, I can't figure out how to tell it to know where to start summing.

I don't know how to write a formula to do that.

Can anyone help?

[Zax63]

I think you want to use the SUMIF function. I don't have a computer with Open Office on it handy but I think it would be:

=SUMIF(E30:E49;">0";G30:G49)

Check this link (http://www.openofficetips.com/blog/archives/2004/12/basic_functions_7.html) for more on SUMIF

[69dodge]

Is your math right, to begin with?

If two dice are rolled and the two results added, the sum will be one of the eleven numbers between 2 and 12. It looks like you're assuming that all of these possibilities are equally likely, but actually they aren't.

The middle number, 7, is most likely, with a probability of 6/36. The probabilities decrease as you move away from 7 in either direction: 6 and 8 each have a probability of 5/36, 5 and 9 have 4/36 each, etc., until 2 and 12 with probabilities of 1/36 each.

[Brainster]

What is your objective here; to come up with a formula that will calculate the odds based on the two numbers you give it, or to come up with reasonable approximations for the relative odds? For simple things like dice rolls, I use the sledgehammer approach with the spreadsheet's random number generator.

I ran each of the 19 meaningful (i.e., not obviously 100% or 0%) combinations 500 times through the spreadsheet, and came out with these relative odds:

0 9 0.4%
0 8 0.8%
0 7 2.0%
0 6 4.4%
0 5 7.2%
0 4 18.6%
0 3 23.0%
0 2 33.6%
0 1 42.0%
0 0 57.6%
1 0 63.4%
2 0 77.2%
3 0 82.2%
4 0 90.0%
5 0 93.8%
6 0 98.0%
7 0 98.8%
8 0 99.6%
9 0 99.8%

Those should be reasonable approximations. I did rerun a couple of the simulations to get rid of random oddities.

[quixotecoyote]

Is your math right, to begin with?

If two dice are rolled and the two results added, the sum will be one of the eleven numbers between 2 and 12. It looks like you're assuming that all of these possibilities are equally likely, but actually they aren't.

The middle number, 7, is most likely, with a probability of 6/36. The probabilities decrease as you move away from 7 in either direction: 6 and 8 each have a probability of 5/36, 5 and 9 have 4/36 each, etc., until 2 and 12 with probabilities of 1/36 each.

Right, and when we're talking about 2 competing rolls of 2 dice together, the odds of a roll which produce a difference of 10 (two sixes and two ones) is 1/121, odds that produce a difference of -10 is the same with the possible combinations reversed, and so on. That's the "out of 121" column.

[quixotecoyote]

What is your objective here; to come up with a formula that will calculate the odds based on the two numbers you give it, or to come up with reasonable approximations for the relative odds?

The first. I'm working on a 2D6 (or maybe 3D6) based homebrew RPG and I need to be able to see the results with modifiers to know how to balance it.


For simple things like dice rolls, I use the sledgehammer approach with the spreadsheet's random number generator.
I ran each of the 19 meaningful (i.e., not obviously 100% or 0%) combinations 500 times through the spreadsheet, and came out with these relative odds:

0 9 0.4%
0 8 0.8%
0 7 2.0%
0 6 4.4%
0 5 7.2%
0 4 18.6%
0 3 23.0%
0 2 33.6%
0 1 42.0%
0 0 57.6%
1 0 63.4%
2 0 77.2%
3 0 82.2%
4 0 90.0%
5 0 93.8%
6 0 98.0%
7 0 98.8%
8 0 99.6%
9 0 99.8%

Those should be reasonable approximations. I did rerun a couple of the simulations to get rid of random oddities.

Maybe I'm a little slow tonight, but what is that table representing?

[quixotecoyote]

I think you want to use the SUMIF function. I don't have a computer with Open Office on it handy but I think it would be:

=SUMIF(E30:E49;">0";G30:G49)

Check this link (http://www.openofficetips.com/blog/archives/2004/12/basic_functions_7.html) for more on SUMIF

That worked. Thanks.

Took me a minute to notice that it was > not >+ and figure out where that last 10% or so went, but I think that's the function I needed.

[69dodge]

Right, and when we're talking about 2 competing rolls of 2 dice together, the odds of a roll which produce a difference of 10 (two sixes and two ones) is 1/121, odds that produce a difference of -10 is the same with the possible combinations reversed, and so on. That's the "out of 121" column.

How did you arrive at 1/121 as the probability of getting two sixes from the first pair of dice and two ones from the second pair?

The probability of getting two sixes from the first pair is 1/36, not 1/11, and likewise the probability of getting two ones from the second pair is 1/36, not 1/11. So the probability of getting both together is (1/36)(1/36) = 1/1296, not (1/11)(1/11) = 1/121.

[Brainster]

The first. I'm working on a 2D6 (or maybe 3D6) based homebrew RPG and I need to be able to see the results with modifiers to know how to balance it.



Maybe I'm a little slow tonight, but what is that table representing?

Dice Roll Modifier 1, Dice Roll Modifier 2, Percentage of the Time that Dice Roll 1 (as modified) is equal to or greater than Dice Roll 2 (as modified). Note that you don't need to do every possible combination of modifiers. For example if you use 4 as DRM1 and 3 as DRM2, it's the same thing as if you used 1 as DRM1 and 0 as DRM2. That is, the net difference between the two numbers is the key, not the actual values.

[quixotecoyote]

How did you arrive at 1/121 as the probability of getting two sixes from the first pair of dice and two ones from the second pair?

The probability of getting two sixes from the first pair is 1/36, not 1/11, and likewise the probability of getting two ones from the second pair is 1/36, not 1/11. So the probability of getting both together is (1/36)(1/36) = 1/1296, not (1/11)(1/11) = 1/121.

Well I may have made a mistake, but I considered the possible results from the first set of dice:

2
3
4
5
6
7
8
9
10
11
12

and the possible set of results from the second set of dice

2
3
4
5
6
7
8
9
10
11
12

There are 11 possible results from each combined roll, so 11*11=121.

Then I subtracted roll 2 from roll 1. Since those possible differences aren't evenly distributed (there's 1 way to get a difference of 10, 2 ways to get a difference a 9, etc.) I used the number of possibilities of getting a result over the total possible combination of results: X/121

[quixotecoyote]

Dice Roll Modifier 1, Dice Roll Modifier 2, Percentage of the Time that Dice Roll 1 (as modified) is equal to or greater than Dice Roll 2 (as modified). Note that you don't need to do every possible combination of modifiers. For example if you use 4 as DRM1 and 3 as DRM2, it's the same thing as if you used 1 as DRM1 and 0 as DRM2. That is, the net difference between the two numbers is the key, not the actual values.

I wonder if 69dodge is right then. My numbers are quite a bit off your numbers.
0 9 2.5%
0 8 5.0%
0 7 8.3%
0 6 12.4%
0 5 17.4%
0 4 23.1%
0 3 29.8%
0 2 37.2%
0 1 45.5%
0 0 54.6%
1 0 62.8%
2 0 70.3%
3 0 76.9%
4 0 82.6%
5 0 87.6%
6 0 91.7%
7 0 95.0%
8 0 97.5%
9 0 99.2%

[Brainster]

I wonder if 69dodge is right then. My numbers are quite a bit off your numbers.
0 9 2.5%
0 8 5.0%
0 7 8.3%
0 6 12.4%
0 5 17.4%
0 4 23.1%
0 3 29.8%
0 2 37.2%
0 1 45.5%
0 0 54.6%
1 0 62.8%
2 0 70.3%
3 0 76.9%
4 0 82.6%
5 0 87.6%
6 0 91.7%
7 0 95.0%
8 0 97.5%
9 0 99.2%

Yeah, if you look at the most extreme examples, you can see it. If DRM1 is 9 and DRM2 is zero, then the only way the second roll wins is if the dice come out 1,1 and 6,6 which is indeed 1/(36^2) or 1/1296, thus the odds of winning would be 1295/1296 or 99.92%.

If we go with the opposite extreme, with DRM1=0 and DRM2=9, then the only way the first dice roll wins is if it is 12 and the second die roll is 3 or less or if the first die roll is 11 and the second die roll is 2. The odds of getting the 12 is 1 in 36, the odds of the other roll being three or less is 1 in 12, so the combination is 1 in 432. The odds of the 11 versus 2 roll is also 1 in 432. So my guess as to the right odds against that happening are 1-((431/432)^2) or .00462.

Keep in mind that my table of numbers may be off too due to sampling error as using the random number generator is effectively the equivalent of polling.

[Brainster]

Your Bell Curve should look more like this:

-10 1
-9 4
-8 10
-7 20
-6 35
-5 56
-4 80
-3 104
-2 125
-1 140
0 146
1 140
2 125
3 104
4 80
5 56
6 35
7 20
8 10
9 4
10 1

Dice Roll #1 Minus Dice Roll #2 (both without modifier), # of times occuring in 1296 rolls. Actually that should have been a quick clue that the distribution you showed was wrong because there was no Bell Curve.

[quixotecoyote]

Your Bell Curve should look more like this:

-10 1
-9 4
-8 10
-7 20
-6 35
-5 56
-4 80
-3 104
-2 125
-1 140
0 146
1 140
2 125
3 104
4 80
5 56
6 35
7 20
8 10
9 4
10 1

Dice Roll #1 Minus Dice Roll #2 (both without modifier), # of times occuring in 1296 rolls. Actually that should have been a quick clue that the distribution you showed was wrong because there was no Bell Curve.

You're right of course. It was far too linear to be a bell curve.

Can you tell me how you figured the possibility per # 1296 rolls? I'm trying to figure out how this works, so I don't want to just grab your numbers.

[Brainster]

You're right of course. It was far too linear to be a bell curve.

Can you tell me how you figured the possibility per # 1296 rolls? I'm trying to figure out how this works, so I don't want to just grab your numbers.

I actually spread out all the possible rolls. That is, you start out figuring out all the possible rolls for the first pair of dice: 1,1; 1,2; 1,3; 1,4; 1,5; 1,6; 2,1; etc until you've got all 36. Then you copy those 36 rolls down the spreadsheet 36 times. Then you match up each set of 36 rolls against each of the possible 36 rolls on the second dice. That is, on the second dice you put thirty six rolls of 1,1 against the 36 possible rolls of the first set of dice as shown above. Then thirty six rolls of 1,2 against the 36 possible rolls of the first set of dice, then 36 rolls of 1,3 against the 36 possible rolls of the first set of dice. So that eventually you have all 1296 possible roll matchups. Then in the fifth column you add the rolls of the first pair of dice and subtract the rolls of the second pair of dice. Then using the countif function I checked how many of those totals were -10, -9, -8, etc.

[quixotecoyote]

-deleted while I rethink the post-

[quixotecoyote]

Ok, let's try this again with everything I've learned so far.

We're going for 2 competing rolls of 3 dice, since that's my end goal.

First I cheated and used an online program to find the likelihood of results from a roll of 3 six sided dice:
3 0.46%
4 1.39%
5 2.78%
6 4.63%
7 6.94%
8 9.72%
9 11.57%
10 12.50%
11 12.50%
12 11.57%
13 9.72%
14 6.94%
15 4.63%
16 2.78%
17 1.39%
18 0.46%


Then I made a matrix of possible outcomes. 3-18 on the X axis and 3-18 on the Y (I can't get the tables to show the labels right).

I then replaced those 3-18's with their respective chance to roll. the 3's became 0.46%'s the 4's became 1.39%'s and so on.

I then made a multiplication table from using those figures:


0.002% 0.006% 0.013% 0.021% 0.032% 0.045% 0.054% 0.058% 0.058% 0.054% 0.045% 0.032% 0.021% 0.013% 0.006% 0.002%
0.006% 0.019% 0.039% 0.064% 0.096% 0.135% 0.161% 0.174% 0.174% 0.161% 0.135% 0.096% 0.064% 0.039% 0.019% 0.006%
0.013% 0.039% 0.077% 0.129% 0.193% 0.270% 0.321% 0.347% 0.347% 0.321% 0.270% 0.193% 0.129% 0.077% 0.039% 0.013%
0.021% 0.064% 0.129% 0.214% 0.321% 0.450% 0.536% 0.579% 0.579% 0.536% 0.450% 0.321% 0.214% 0.129% 0.064% 0.021%
0.032% 0.096% 0.193% 0.321% 0.482% 0.675% 0.804% 0.868% 0.868% 0.804% 0.675% 0.482% 0.321% 0.193% 0.096% 0.032%
0.045% 0.135% 0.270% 0.450% 0.675% 0.945% 1.125% 1.215% 1.215% 1.125% 0.945% 0.675% 0.450% 0.270% 0.135% 0.045%
0.054% 0.161% 0.321% 0.536% 0.804% 1.125% 1.340% 1.447% 1.447% 1.340% 1.125% 0.804% 0.536% 0.321% 0.161% 0.054%
0.058% 0.174% 0.347% 0.579% 0.868% 1.215% 1.447% 1.563% 1.563% 1.447% 1.215% 0.868% 0.579% 0.347% 0.174% 0.058%
0.058% 0.174% 0.347% 0.579% 0.868% 1.215% 1.447% 1.563% 1.563% 1.447% 1.215% 0.868% 0.579% 0.347% 0.174% 0.058%
0.054% 0.161% 0.321% 0.536% 0.804% 1.125% 1.340% 1.447% 1.447% 1.340% 1.125% 0.804% 0.536% 0.321% 0.161% 0.054%
0.045% 0.135% 0.270% 0.450% 0.675% 0.945% 1.125% 1.215% 1.215% 1.125% 0.945% 0.675% 0.450% 0.270% 0.135% 0.045%
0.032% 0.096% 0.193% 0.321% 0.482% 0.675% 0.804% 0.868% 0.868% 0.804% 0.675% 0.482% 0.321% 0.193% 0.096% 0.032%
0.021% 0.064% 0.129% 0.214% 0.321% 0.450% 0.536% 0.579% 0.579% 0.536% 0.450% 0.321% 0.214% 0.129% 0.064% 0.021%
0.013% 0.039% 0.077% 0.129% 0.193% 0.270% 0.321% 0.347% 0.347% 0.321% 0.270% 0.193% 0.129% 0.077% 0.039% 0.013%
0.006% 0.019% 0.039% 0.064% 0.096% 0.135% 0.161% 0.174% 0.174% 0.161% 0.135% 0.096% 0.064% 0.039% 0.019% 0.006%
0.002% 0.006% 0.013% 0.021% 0.032% 0.045% 0.054% 0.058% 0.058% 0.054% 0.045% 0.032% 0.021% 0.013% 0.006% 0.002%


I then changed the X and Y axis labels BACK to 3-18 and 3-18

To the side of this table I made a second table of the possible differences of subtracting the first roll from the second, -15 to 15. To determine the chances for each one, I summed all of the percentages I got from the multiplication table which corresponded to a given difference.

thus 14 was (.006%+.006%) 13 was (.013%+.019%+.013%) etc. I had more exact figures so these tables show a little rounding error:

15 0.002%
14 0.013%
13 0.045%
12 0.120%
11 0.270%
10 0.540%
9 0.977%
8 1.620%
7 2.488%
6 3.571%
5 4.816%
4 6.121%
3 7.354%
2 8.372%
1 9.047%
0 9.285%
-1 9.047%
-2 8.372%
-3 7.354%
-4 6.121%
-5 4.816%
-6 3.571%
-7 2.488%
-8 1.620%
-9 0.977%
-10 0.540%
-11 0.270%
-12 0.120%
-13 0.045%
-14 0.013%
-15 0.002%


Then I used Zax63's method of summing the results to see when the first roll is equal to or greater than the second roll.

Taking modifiers into account resulted in this table of results displayed in the format Brainster used earlier:

0 9 1.97%
0 8 3.59%
0 7 6.08%
0 6 9.65%
0 5 14.46%
0 4 20.58%
0 3 27.94%
0 2 36.31%
0 1 45.36%
0 0 54.64%
1 0 63.69%
2 0 72.06%
3 0 79.41%
4 0 85.54%
5 0 90.35%
6 0 93.92%
7 0 96.41%
8 0 98.03%
9 0 99.01%


Can anyone see any problems with this go-around.

If anything's unclear, let me know.

[69dodge]

Looks good. I got the same numbers.

[quixotecoyote]

Looks good. I got the same numbers.

Excellent. Thanks for the help.