Generating Random Numbers with a Normal Distribution in Excel

  I wanted to test the transverse least squares fit method with some normally distributed errors but Excel doesn't have a random number generating function that will do this. What it does have is the function NORM.INV which will compute the inverse of the normal cumulative distribution function and the RAND function which will generate uniformly distributed random numbers between 0 and 1 and so one can use NORM.INV(RAND(),μ,σ) to generate a set of normally distributed random numbers with mean μ and standard deviation σ. But the result sometimes looks a little strange.

This is because the resulting random numbers produced by the RAND function are subject to statistical fluctuations and its histogram will not have uniform counts.

In the example above the expected value of the count in each partition is about 25. One can get more uniform RAND counts by generating the same number of random numbers for each interval of the histogram.

The resulting set of random numbers is closer to a normal distribution.

An array with 25 rows and 40 columns was used to contain the random numbers above. If p=40 is the number of partitions and k=0 to 39 is an index specifying the columns the formula used for each cell in the array is [k+rand()]/p. One can use the OFFSET function to stack all the cells in a single column but the order is not random and one has to use RAND again to place another random number next to each of the numbers. Both columns can be copied and their numerical values pasted in two new columns and which are then sorted by the second ordinal column. This changes the order of the set of random numbers like one would do in shuffling a deck of cards.

Supplemental (May 26): I've been trying to come up with a descriptive name for the modified probability distribution and "tempered" in the sense of "duly proportioned" seems to be a good choice. Here are some of the steps showing the transfer the table of random numbers into a single column, the addition of a ordinal number and the copy, paste and sort for the set of tempered normal random numbers, rnums.