Excel dice

[Inconsequenti-AL]

If it's any use, this macro should get round the 'Rerolling' thing without a lot of Copy/Paste Special.

Copy this routine and paste it into the macro editor. You can edit the 2 const lines to change the number and size of the dice, which is a slightly cumbersome, but it should work.


Sub RandomNumberMarco()
'
' Macro written by Inconsequenti-Al
'

' This macro will create NumRolls random numbers of
' dice with DiceSize faces stored in column D of a spreadsheet.

' Don't make NumRolls larger than 65536 or you will run out of rows and crash!

Const DiceSize = 20
Const NumRolls = 100

Cells(1, 1) = DiceSize
Cells(1, 2).FormulaR1C1 = "=TRUNC((RAND()*RC[-1]),0)+1"

For loop1 = 1 To NumRolls
Cells(loop1, 4) = Cells(1, 2).Value
Next loop1

End Sub

 

[Ayrk]

You should be able to go under Tools - Options and turn Automatic Recalculation off. Then whenever you want the new rolls, you just press F9. Of course that does mean that no formulas will calculate until you press F9.

 

[ichabod]

For what it's worth, I was working on an excel sheet to do a chi-squared test on randomness of dice (based on the old dragon article).

For fun, I had excel randomly fill a few hundred cells and tested the randomness of excel's random number generator. (I use it for a few things in game.) It came out being nicely random so you don't have to worry about bias.

This is actually incorrect. The chi-squared test for goodness of fit is good for saying that a random set of numbers is random, but it's not so good at noticing that a non-random set of numbers is non-random. If you use excel to make a whole slew of random numbers, and then subtract the nth random number from the random number after it, you will expect a certain distribution. Say you do random numbers from 1 to 10. There are 100 possible pairs of numbers. 1% of them should difference to -9, as 1 and 10 is the only combination that will do so. 2% of them should difference to -8, as (1,9) and (2,10) are the only two combinations that will give you -8. And so on. If you check the distribution of the differenced values with the chi-squared test, it fails the test. This is especially significant given how much trouble the chi-squred test has detecting a bad distribution.

What does this mean? This means that each roll is highly dependent on what the last roll was. Not too surprising giving that Excel uses an itterated function to make the random numbers, but it is a definite weakness of such random numbers.

 

[Zarthon]

I use a dice roller for excel that I downloaded ages ago (I have attached it and an example of the sheet I use), all my encounters are put together in excel before my games, I then just print it out.

If you have alook at the sheet you can put your encounter/room number along the top and the creatures below or use whatever system you choose.

Hope it helps.

ps: I dont remember where I found it, and I hope that Im not breaking any copyright thing by posting it here.

 

[Ferret]

Any one know how to turn die rolls into bell curves?

 

[Sir Whiskers]

Any one know how to turn die rolls into bell curves?

From Inconsequenti-Al, above:

Take 4 random numbers in cells A1-A4. Use formula:

=SUM(A1:A4)-MIN(A1:A4)

gives you roll 4, drop the lowest.

You can modify it to just use 3 numbers, or 20, or however many you need.

 

[ichabod]

Any one know how to turn die rolls into bell curves?

I think this will work:

=ROUND(NORMINV(RAND(),3.5,1),0)

That should give you a random variable from 1-6 with a bell curve distribution. This will occaisonally give you a 0 or a 7 (about 1% of the time). You could lower this chance by shrininking the 1, but it's always going to be there. Or you can do a two cell formula, with the second cell being:

=IF(A1<1,1,IF(A1>6,6,A1))

Replacing A1 with whatever cell the first formula is in.

What if you want another die? Add one to the number of sides, and divide by two. That number replaces 3.5 in the first formula. Divide the number of sides by six, and replace the 1 with the result. For the standard dice, this gives you:

d4: =ROUND(NORMINV(RAND(),2.5,0.66),0)
d8: =ROUND(NORMINV(RAND(),4.5,1.33),0)
d10: =ROUND(NORMINV(RAND(),5.5,1.66),0)
d12: =ROUND(NORMINV(RAND(),6.5,2),0)
d20: =ROUND(NORMINV(RAND(),10.5,3.33),0)
d100: =ROUND(NORMINV(RAND(),50.5,16.66),0)

 

[Piratecat]

I'll slide this over to the software forum.