QUICKLY GENERATING NORMAL PSEUDORANDOM NUMBERS USING PREBUILT EXCEL FUNCTIONS
The fastest method to create a normal pseudorandom number in Excel is to leverage Excel's prebuilt functions. Learning the RAND function, which creates a uniform pseudorandom number, is the first step, while understanding the NORMS-INV function is the second. The NORMSINV function returns the inverse of the normal distribution, when provided with a probability. The question, “What is the inverse of the normal distribution?” often follows the introduction of the NORMSINV function. To help explain the function we will look at a more intuitive distribution function and use a tangible example to explain how both functions work.
FIGURE 2.5 The NORMSDIST function provides the value under the curve given a point on the distribution. Here the value under the curve for 1 is visually shown.
The easier normal distribution function to learn first is the NORMSDIST function. If one were to enter “=NORMSDIST(1)” in a cell and press ENTER, the value .84135 would be returned. The NORMSDIST function returns the cumulative area under a normal distribution curve, up to the standard deviation value selected, assuming a distribution with a mean of 0 and a standard deviation of 1. Thus putting a 1 in the parameter for the function returns .84135, meaning that .84135 of the area under the curve is captured up to the first standard deviation. This is visualized in Figure 2.5.
To further explain this, enter “=NORMSDIST(-1)” in a cell and press ENTER. The value .15865 is returned because the area under the normal distribution curve to −1 is .15865. See Figure 2.6.
FIGURE 2.6 Notice that the function is cumulative in nature starting with lower bounds. Here, when −1 is used as the parameter the area returned only goes up to where −1 would fall on the normal distribution.
FIGURE 2.7 A figure that might be familiar from statistics classes is returned when we look at the probability of being within two numbers on the normal distribution.
Notice that if we add the two values we just calculated we get 1, which is always the total value of the area under the normal distribution. Now, given the symmetrical nature of the normal distribution, most people talk about probability in regards to the normal distribution by saying, “What is the probability of being WITHIN “x” standard deviations of the mean?” NORMSDIST can provide this by subtracting the function return with a parameter of –1 from the function return with a parameter of 1. If you do this in Excel you will get a value of .68268, meaning that there is a 68.268 percent chance of being within one standard deviation for a sample value drawn from a population exhibiting a normal distribution with a mean of 0 and a standard deviation of 1. See Figure 2.7.
In the series of previous examples we provided a data point on the normal distribution and had the NORMSDIST function return the cumulative probability. In a more tangible example we could have been talking about heights and wanted to know the probability that a certain height is exhibited out of a population of heights. In our example earlier we would have been providing the heights and getting the probability returned from the function. However, what if we wanted to the opposite, that is, provide the probability and have the height returned? We can do this using the NORMSINV function. Test this out by entering “=NORMSINV(.84135)” in a cell. The returned value will be very close to 1, which was the parameter we used earlier with NORMSDIST to get the .84135 value.
MODEL BUILDER 2.4: Creating Normal Pseudorandom Numbers Using Excel Functions
- In the Model Builder file that you have been creating, insert a new worksheet and label it “Normal Pseudorandoms from Sht”.
- In cell C5 enter “=NORMSINV(RAND())”. Notice that earlier we had said that NORMSINV returns the value on the normal distribution given a probability. RAND provides a pseudorandom number between 0 and 1, which can be thought of as a random probability. This produces a normal pseudorandom number. Copy and paste the formula over the range C5:C104.
FIGURE 2.8 The NORMSINV function combined with the RAND function can quickly produce a set of normal pseudorandom numbers.
- As with the Uniform Pseudorandoms from Sht sheet make a histogram to summarize the data. To do this we should check the minimum and maximum of the data set returned from the newly entered functions. Generally the minimum and maximum should be around –3 and 3 respectively. In E6 enter −3 and grow it by 1 in each successive cell to the right until it reaches 3.
- Use the same formulas as the “Uniform Pseudorandoms from Sht” sheet to count the number of data points in the data set that fall within the buckets created in step 3. The sheet should look like Figure 2.8 thus far.