Excel dice

[apsuman]

What is the best way to build a d6 die result in excel?

g!

 

[orsal]

What is the best way to build a d6 die result in excel?

g!

=int(6*rand())+1

 

[apsuman]

=int(6*rand())+1

Thaks, I couldn't remember int


g!

 

[Hjorimir]

Is there a way to force the roll? Could you setup combat simulators doing this?

 

[BryonD]

=Randbetween(1,6) will also do it. And it is easier to remember.

 

[Sir Whiskers]

I noticed something interesting. I have a Rand formula in several cells. When I cause one to generate a random number, they all do so. Any idea why?

I was thinking of setting up different lines in Excel for separate characters, then using a macro to "roll" for a given character - looks like the program will roll for all of them, not just one.

edit: I figured out that when forcing a roll I was causing the entire sheet to recalculate. Interesting, I didn't know it worked that way...

BTW, you need the Analysis ToolPak for the Rand function to work. If it's not already installed, in Excel go to Tools - Add Ins to install it.

 

[orsal]

edit: I figured out that when forcing a roll I was causing the entire sheet to recalculate. Interesting, I didn't know it worked that way...

I think any time you enter a formula in a cell, formulas get recalculated -- and any time the formula involves a random number, recalculation includes rerolling. This can be both a bug and a feature.

It is possible to freeze rolls -- say, if you want to randomly roll ability scores and not get them changed -- by copying and "paste special--paste as values"; that will replace the random-number formula with whatever value is in the cell at the time. On the other hand, the automatic reroll is really convenient if you want to do simultaneous spot checks for a large party.

 

[Inconsequenti-AL]

Yay. Excel geekery!

For a 6 sider. I use:

=TRUNC(RAND()*6,0)+1


We used Excel to calculate shots from very large units of enemies. i.e. 200 orcs firing at 4 high level PCs.

Simply make a large block of random numbers. Then place these formula under it:

=countif($A$1:$Z$10,"1")
=countif($A$1:$Z$10,"2")
... repeat for 3-19
=countif($A$1:$Z$10,"20")

gives a count of each number rolled. Can be useful for large scale stuff where you don't want to count them all by eye.


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

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

gives you roll 4, drop the lowest.

/Excel geekery.

 

[Frostmarrow]

I noticed something interesting. I have a Rand formula in several cells. When I cause one to generate a random number, they all do so. Any idea why?

I was thinking of setting up different lines in Excel for separate characters, then using a macro to "roll" for a given character - looks like the program will roll for all of them, not just one.

edit: I figured out that when forcing a roll I was causing the entire sheet to recalculate. Interesting, I didn't know it worked that way...

BTW, you need the Analysis ToolPak for the Rand function to work. If it's not already installed, in Excel go to Tools - Add Ins to install it.

I usually do a lot of dice on one page in the book and then I get one of those numbers for all dice rolled on the rest of the pages (where the actual "combat" takes place). Still this does not rid you of the problem entirely.

If you copy a cell with a random number and then go paste special you can chose to copy just the value and not the whole function, so to speak. If there is a way to "paste special" in a function (or macro) you could produce random numbers and then store the numbers in hard coded lists. Then refer to the lists for the results. Sadly I can't seem to find a way of "pasting special" in the function and macros are a hassle. Still, this might give you further ideas.

 

[Zad]

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.