Home » Forums » Open Source Learning Center » Question and Answer

Random 3D6 Die-Roll and Test

Submitted by mattymanc on 6 January, 2011 - 00:15

I am moderately experienced in Excel, but am really struggling with a task I think may be simple and would appreciate everyone's help.

I have a worksheet called Stats, and upon this I have Cell A1 named Fortune and on cell B1 a value of 12.
 
On a separate worksheet, lets call it Sheet2! I have a text box which explains to my students that they need to Test their Fortune.
 
What I want (without VBA or a Macro as they have been disabled in the campus) is to offer a student a button, shape or hyperlink (with a formula) that when clicked will randomly roll a number from 1 to 18, present the results in a cell beneath the button, shape or hyperlink and then check if the randomly rolled value against the Fortune value on the Stats! worksheet.
 
If the Random value is less than or equal to the Fortune value the student is fortunate (this would be displayed). 
If the Random value is greater to the Fortune value the student is unfortunate (this would be displayed). 
 
I have produced a random number with the formula:
=RAND()*18
 
But am unsure how to tie the two together,
 
Please help?
 

‹ Automating gray overlay in a graph VBA excel to communicate with external SQL database ›
»
Nick's picture

Random Number Generation

Submitted by Nick on 7 January, 2011 - 10:42.

Hi Matty

To Create a random number between 1 and 18, you need this formula:

=ROUND(((RAND()+1/36)*18),0)

- this will have an expectation of 9.5 (the average of 1 and 18)

 

This formula:

=RAND()*18

.. has an expectation of 9, and will not give you a round number..

 

If VBA is turned off, there's no way to use a customised button.

I would recommend telling them to press F9

- this will recalculate the formula and all dependencies

 

 

 

»