Excel uses the random number generator of most computers to generate numbers randomly from the range of numbers 0-1. This means Excel might generate a number like .4534582 or .0274924 but not 38 or -2. Each number generated must fall between 0 and 1. If you need random numbers outside this range, you have to perform a little arithmetic on it first.

The format for the random number function is:

=RAND()

This function does all that is necessary to produce numbers at random. We shall look at some arithmetic processes you could use to get random numbers from your range.

We will look at the process of getting a random number from the range of 1 - 10 as an example of how to modify the rand function.

Steps	Function	Results #1	Results #2
1	=RAND()	.0345739	.3829847
2 - Multiply by 10	=10*RAND()	0.345739	3.829847
3 - Apply INT function	=INT(10*RAND())	0	3
(Note the INT function returns the Greatest Integer Less than the argument) INT(3.48574) = 3 and INT(1.5683) = 1 and INT(.05935) = 0
4 - Add 1	=INT(10*RAND())+1	1	4

Discussion

• Step 1 simply introduces the random number function and we see two possible results so you can follow what might happen in each case.
• Step 2 multiplies the random number by 10, the largest value in the range 1 - 10. This could produce values as small as .000001 or as large as 9.9999.
• Step 3 computes the greatest integer less than the actual value. In this case we get the 0 and 3.
• Step 4 adds 1 to the results from step 3 to insure that the output is a number at random from 1 - 10 which was the goal of the process.

In general, the process for finding a random number 1 through N would be to evaluate

=INT(N * RAND()) + 1

Desired Range	Random Number Function
1 - 6	=INT(6 * RAND()) + 1
1 - 100	=INT(100 * RAND()) + 1

So what!

Why would anybody ever want to use the Random Number Function? Great question. The best answer is ... Most Don't!

For the occasions that you do, you are quite pleased to have a program like Excel to let you generate numbers at random. One example of where such a need arises is in the world of probability. You are often called upon to model or simulate real world events with a mathematical equivalent. Take the event of rolling a pair of dice.

Each die is a cube with 6 faces having 1 to 6 dots. In a fair roll, each of these faces is equally likely to appear face up. The mathematical model for this would be a random number from the range of 1 - 6. A pair of dice would be two random numbers. The dice roll is the sum of the number of dots that are face up on the two cubes.

This is an example of a mathematical model of rolling 10 pairs of dice. Column D displays the total of the dice roll.

The formulas in Columns B and C are identical: =INT(6 * RAND())+1

The formula in Column D indicates Excel should add the two values in Columns B and C respectively.

The Function Key F9 will cause a re-calculation of all formulas and functions on the spreadsheet. In this way you could see the results of another set of 10 dice rolls.

In a study of probability, you are interested in the frequency of the occurrence of an event. In this case, we will look at the frequency of "rolling" each of the possible values 2 - 12.

We simply add a section to this piece of work to count what is happening across the 10 simulated rolls of the dice.

Column F contains the list of possible Dice Rolls. Of interest is the formulas/functions in Columns G and H. Let's see how these cells get there.

We use the Countif function from the Counting Functions category in Column G. We ask Excel to count the number of occurrences of 2 in the range of cells from F2 to Fll with the function:

=COUNTIF(F2:F11,"=2")

and the function calls for Excel to look in Column F from Cell F2 to Cell F11 and total the number of cells having a value equal to 2. In this example, we find the number of 2's is zero. That result is displayed in G2.

A similar function is placed below referencing F2 through F11 with the conditions "=3", "=4", "=5", ... , "=12". This gives us a count of how many times each of the rolls came up at random in 10 simulated dice rolls.

Column H makes use of the Repeat function. The asterisk (*) is repeated as many times as the frequency count in Column G. This function is found in each of the cells in Column G

=REPT("*",G2)

for the graph of the Frequency of 2. This function is copied to the cells below producing the expected =REPT("*",G3) and so forth down to =REPT("*",G12). That's how you get a string of ***'s representing the frequency of rolling each of the values 2-12 when you take 10 rolls of the dice.

In the world of probability, 10 measurements is a little small. Suppose we increased the number of dice rolls to 100. The Excel work is identical.

The end of range has to be changed in each of the cells in Column G to reflect the last cell for the 100th simulated dice roll. Who would want to roll a pair of dice 100 times and keep up with what happens when you could just as easily make Excel do it. Think how easy it is (press F9) to do the entire experiment over again!

Return