Excel help: dice distribution curve charts?

[CarlZog]

I'm trying to make some charts showing the distribution curves for two different dice added or subtracted. For example, 1d20+1d6: What would the distribution of the possible results look like? Or 1d20-1d4? etc.

I thought this would be easy to do in Excel, but I'm not at all well-versed in the program and I can't figure out how to generate the data -- short of manually calculating all the possible results.

Anybody know an easy way to do this for a variety of dice combos?

Carl

 

[orsal]

I'm trying to make some charts showing the distribution curves for two different dice added or subtracted. For example, 1d20+1d6: What would the distribution of the possible results look like? Or 1d20-1d4? etc.

I thought this would be easy to do in Excel, but I'm not at all well-versed in the program and I can't figure out how to generate the data -- short of manually calculating all the possible results.

Anybody know an easy way to do this for a variety of dice combos?

Carl

Instead of manually calculating all the results, let Excel calculate all the results? I'll show you how to do it quickly for 1d20+1d6, and you can make the obvious changes for other dice.

In cell A1, enter 0, then with the cursor in that cell use edit-->fill-->series, in columns, type linear, step value 1, stop value 119. This should give you all the numbers from 1 to 119 (using 119 because it is 20x6-1) in column A. In cell B1, enter: =int(a1/6)+1. In cell C1, enter: =a1-6*int(a1/6)+1. Copy both those cells down to the bottom row of your table. You should now have every pair of results for a d20 and d6 in columns B and C. In column D, you can add them.

Then, in another part of the spreadsheet (say, beginning in column F1) do another table. In column F, do an edit-->fill-->series to list all possible values for whatever calculation you did in column D (in this case, from 2 until 26). In G1, enter: =countif(D$1$120,F1)/120
. This will give you the probability of an entry in column D being exactly the value listed in cell F1. Then copy that down to G25 to get the rest of them.

 

[CarlZog]

Orsal:

Thanks so much, but I lost you on the formula for cell G1. It looks like enworld translated your punctuation into a smiley face!

How is that supposed to read?

Edit: Never mind. I figured out the smiley translation.


Thanks again. This is exactly what I was trying to do!


Carl

 

[DaveStebbins]

Yup, I've used the finite number of unique and equally probable results to crank out exact probabilities for 4d6-L, 5d6-2L, 4d5-L+3, etc. Copy and fill are your friends in Excel.