Normal distribution is one of the most commonly found distribution types in nature. The normal distribution is a continuous probability distribution where the data tends to cluster around a mean or average. If you were to plot the frequency distribution of a normal distribution, you will tend to get the famous inverted bell-shaped curve also known as the Gaussian function.
Coming to the point, we are sometimes faced with situations where we would like to test out a hypothesis or carry out some experiment on a dummy set of normally distributed data points. To create a normally distributed set of number in Excel, you can use this formula:
=NORMINV(RAND(), mean_value, standard_deviation)
Ex. =NORMINV(RAND(), 10, 50)
All you need to do is to enter this formula in a single cell and copy to as many cells as you want.
mean = is the simple average of the given set of numbers
standard deviation = measure of the dispersion of a set of data from its mean
RAND() = Excel function that generates a random fractional value between 0 and 1 (both included)
How does this formula for generating normal distribution work ?
The formula NORMINV() function in excel basically generates a number that has a probability represented by RAND() and which belongs to a normal distribution curve with a mean represented by mean_value and standard deviation represented by standard_deviation in the above formula. Now RAND() basically generates a fractional random number between 0 and 1 and therefore can be used to generate a random percentage figure between 0 and 100%. Hence by using both the RAND() and the NORMINV() functions together, we can create a set of numbers that are normally distributed over the entire range (0 to 100%) of a normal distribution curve with a given mean and standard deviation.