[Solved] Calculating probabilities seems to be off in just one copy...

[VictorB]

I made a few tables in Calc to visualize the probability of rolling dice in order to see which option would yield me the better result: A single 8-sided die, two 4-sided dice, a single 12-sided die, or two 6-sided dice. After making a few additional tables to gather all the variable dice rolls, I made 8 nearly identical summary tables to extrapolate the values for each option, as well as seeing how adding a +2 modifier to the roll would change things.

With variations of the formula " =(COUNTIF(B20:E23,"<7"))/(SUM(S24:S49)) " and " =(COUNTIF(B20:E23,">=7"))/(SUM(S24:S49)) ", I was also able to see how many of the roll possibilities would be found lower or higher than median dice roll (in this case, 7), and use it to verify the count as both would equal 100%... except in the exact case I listed above. There is nothing different about that formula than any other option, but the first value comes out at 2/5ths or 40%, and the second value comes out at 2/3rds or 66.67%, which I find odd.

Not knowing where I went wrong, I've attached the document and highlighted the problem area in red. Any help would be greatly appreciated.

[John_Ha]

I made a few tables in Calc to visualize the probability of rolling dice in order to see which option would yield me the better result:

Please describe EXACTLY what result you are looking for because I cannot see how the different dice can be compared. For example

• an 8-sided die will give numbers 1 to 8
• two 4-sided dice will give numbers 2 to 8
• a 12-sided die will give numbers 1 to 12
• two 6-sided dice will give numbers 2 to 12

Whereas the single dice will give a uniform distribution of numbers the pairs of dice will give a very non-uniform distribution. With two 6-sided dice there is no combination which will give 1; only one combination which will give 2 (1,1) or 12 (6,6); and five combinations which will give 6 (1,5) (2,4) (3,3) (4,2) (5,1). You will therefore get a result of 6 five times more often than you will get a result of 2 or a 12. The frequency distribution of the result for two dice is a "bell shaped" curve whereas the frequency distribution of the result for a single die is a flat line.

Clipboard02.gif (13.44 KiB) Viewed 703 times

[VictorB]

Please describe EXACTLY what result you are looking for because I cannot see how the different dice can be compared.

Sorry, tried to be a little more professional than drone on with nerdiness. I am calculating the best weapons to use in D&D. I hear a lot of opinions that a sword and board character with the Duelist fighting style (giving a single-handed 1D8 weapon a +2) is best for a fighter or paladin, while a barbarian would benefit more from either a Greatsword (2d6 straight) or Greataxe (1d12). I have also heard of an alternative flavour for higher-level characters where you take a 1d8 weapon and split it among two dice (2d4) in order to gain a bell curve and better results.

The issue is that I am having a hard time wrapping my head around the numbers. I am currently making an Oath of Vengence Paladin, so he'll be right there in the fray, and I want to make him the most effective with every hit, keeping the higher chances that he does great damage over the long run. So, I decided to do the calculations for myself to try and better visualize and understand the issue at hand, seeing why some opt in for linear distribution of a single dice and some for the bell curve.

For example

• an 8-sided die will give numbers 1 to 8
  two 4-sided dice will give numbers 2 to 8
  a 12-sided die will give numbers 1 to 12
  two 6-sided dice will give numbers 2 to 12

Actually, that's where things got interesting. In the document provided, I showed that the average value of a straight roll for each are
4.5 for a 1D8, 5 for a 2D4, 6.5 for a 1D12, and 7 for a 2D6, suggesting that the 2D6 would hit harder. However, when I add the +2 modifier (adding 2 to the value of the dice's face) to the first two thanks to Duelist, the averages change to a 6.5 for both a 1D8 and a 1D12, and a 7 for a 2D4 or a 2D6. This suggests that, though the 2D4+2 would have a max roll of 10 points, it would usually do the same amount of damage as a 2D6.

The next place I thought of looking was to see how likely am I going to hit above that average. Maybe that would show where one trumps the other, by either keeping rolling the max damage at the same probability as a 1, or to give up on the likeliness that I roll max value for a better chance to roll a decent value... Which is where I caught my snag. I added a calculation to figure out the percentage of rolls that, in this case, the 2d4+2 would roll above its average/median value, and again to calculate the percentage that would be above the average. In all other 7 calculations, both percentages add up to 100%, which signifies to me that I did it right. However, with the 2D4+2, the percentages come up to 106.666666% leaving me unsure what went wrong.

I came here to verify my work on those last couple of calculations so that I could move on to seeing if there are more variables that might change the outcome. Hope that clarifies the issue.

[karolus]

Its at least very simple two six-side draws produce 36 different Kombinations, only one of them gives a sum of 2 … only one a sum of 12… but there 6 different Kombinations for the sum=7 … 5 Kombis for sum 6 or 8 … 4 kombis for sum 5 or 9 … 3 kombis for 4 or 10 … 2 kombis for 3 or 11

[John_Ha]

You missed out a line in your analysis. The lowest value in that range must be 1 + 1 + 2 = 4 whereas you had 5. It was pretty obvious once I searched properly as the pattern in that range was not symmetric as it is for the other three ranges which alerted me where to look. Once I added it the sum is 100% as expected though you should check I have added it correctly.

My apologies - I should have looked more thoroughly at your file before responding above ...
.

The arrowed line was missing in your file

Clipboard02.gif (17.1 KiB) Viewed 644 times

Showing that a problem has been solved helps others searching so, if your problem is now solved, please view your first post in this thread and click the Edit button (top right in the post) and add [Solved] in front of the subject.

[VictorB]

You missed out a line in your analysis. The lowest value in that range must be 1 + 1 + 2 = 4 whereas you had 5. It was pretty obvious once I searched properly as the pattern in that range was not symmetric as it is for the other three ranges which alerted me where to look. Once I added it the sum is 100% as expected though you should check I have added it correctly.

Hey, thanks for the reply. It's my turn to feel very embarrassed as I was so focused on my formulas at the bottom that I was utterly blinded from looking at the data used. I will make sure to be much more vigilant on such matters in the future.

And I apologize for the late reply, as my father had gone to the hospital and passed away. I will mark this solved.