This function is for generating random numbers. Instead of simply using Excel’s built-in random number generation algorithm, Excel Power Expander has its own set of built-in random number generation engines, which are far more advanced than Excel’s algorithm and provide a lot of control over random numbers being generated.
“Random Number Generator” is divided into two modes : Normal Mode and Advanced Mode. Each of these provides different features to meet different requirements of random numbers generation under different situations. The program will guide you through the whole process step-by-step.
“Random Number Generator” is very fast. It can easy draw several thousand numbers in 1 second. Such speed makes it a perfect tool for building complex analysis or simulation models.
Identical Sets Random Numbers?! Not Kidding!
In Advanced Mode, with the "random seed" implementation, you can draw multiple sets of random numbers which are perfectly identical. (Just make sure you remember you random seed. It's your "key".)
The “Normal Mode” of “Random Number Generator” was designed for quick generation of random numbers, which do not necessarily have to follow any particular statistical distribution. Or, you can say that this mode is for less advanced situations which do not require strict statistical control over the numbers being drawn.
However, the Normal Mode still provides quite a number of basic controls over the random numbers. For example, you can decide to draw:
Also, other controls include:
Below shows a sample screen of the Random Number Generator in Normal Mode. (Note that the “Normal” tab is currently active in the figure.)
The “Advanced Mode” of “Random Number Generator” is useful in situations when advanced statistical controls over random numbers are required. With its built-in advanced random number generation engines, it is capable of drawing numbers which follow user-specified distribution, mean and standard deviation, etc.
Also, with the implementation of ”Random Seed”, it can reproduce the identical sets of numbers at any time and as many times as you need. “Random Seed” can be think of as a “key”. It is a number you chose by yourself, which can be re-used for producing the same, identical set of random numbers. (This will be demonstrated in the next sub-section 9.1.3.)
It is very rare to see such type of random number generation engines being implemented in Microsoft Excel. It is indeed a good news that Excel users can now enjoy such sophisticated random number algorithms.
Distribution Description Input Parameters Unit Uniform Draw numbers between 0 and 1. Similar to Excel’s built-in RAND() function, and the random number function of most calculators. NIL Normal Draw numbers which follow a Normal Distribution, satisfying user-specified mean and standard deviation. Mean,
Log Normal Draw numbers which follow a Log Normal Distribution, satisfying user- specified mean and standard deviation. Mean,
Exponential Draw numbers which follow an Exponent Distribution, satisfying user- specified mean and standard deviation. Mean Discrete Value Range
Also, other controls include:
Below shows a sample screen of the Random Number Generator in Advanced Mode. (Note that the “Advanced” tab is currently active in the figure.)
Let’s have a demonstration of how the “Random Number Generator” of Excel Power Expander differs from any random number generators for Excel.
Below, you’ll be shown, step-by-step, how to use the “Random Number Generator” function to perform three challenging real-life random number generation tasks.
For example, in a computer simulation model, we need to produce 2000 random numbers. The numbers must follow a normal distribution, and with mean of 50 and standard deviation of 5.5. We need to choose a “random seed”. This is a “key” which can be re-used to generate the same set of random numbers in the future. Here, we use “13” as random seed.
Summary of Random Numbers to Generate: Mode Advanced Mode Distribution Normal Distribution Random Seed 13 Numbers to Draw 2000 Number of sets to draw 1 Sort order of output No Mean 50 Standard Deviation 5.5
We fill-in the spaces provided as shown in the figure in the next page. The, click OK to draw the random numbers.
The screen capture below shows the output result. (You can actually compare your result with the numbers in the figure below. They should be identical if you also used the random seed of 13.)
Action 2: Drawing an identical set of random numbers
Now, we try to draw an identical set of 2000 random numbers in Column C. With cell C1 selected, run the Random Number Generator function again, and fill-in the spaces exactly the same as in the previous example. Remember to use the same random seed of “13”. You should see an identical set of numbers being drawn in Column C. We can then try to prove that the numbers in the two columns are in fact identical. In Column E, we work out the difference between the two columns. Then with the column E selected, we note the “Total” for column E. (As shown in the figure below.) We see that the summation of the 2000 “differences” is 0 (zero).
A sample file for this section is including in the Excel Power Expander software, in the file: “Random Numbers_Discrete.xls” in the “Examples” folder.
Suppose we’re building a computer simulation model for a fast food store. In one part of the model, we need to create random events for customers entering the store. The table below shows the probability of different types of customers that will enter the store:
(IMPORTANT : All probabilities should add up to 1.)
Fill-in the spaces as shown in the figure below. (For the “Value Range” and “Probability Range”, you can either type the addresses or drag on the spreadsheet using the mouse.) Click OK to proceed.
(If you would want to see a sample result of this draw example, you can refer to the second worksheet in the file “Random Numbers_Discrete.xls”.)