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.
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.
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.