Categorized | excel formula, excel tips
Tags |

How to generate a sample set with normal distribution in Excel




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 ?

generate-numbers-for-normal-distribution-dataThe 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.


Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>


Comments and Trackbacks

  1. Jon Peltier wrote:

    To make this even more flexible, enter the desired mean in cell A1 and the desired standard deviation in A2, and reference these values in your formula:

    =NORMINV(RAND(), $A$1, $A$2)

  2. Jithin wrote:

    If the above is true, then after we generate the random set of data, its mean and standard deviation should be 10 and 50 respectively. But thats not what comes if u try to find it for the set. Try it.

  3. Theuns wrote:

    The answer should not be excactly what you give it i.e. 10 and 50, but it is close to the original values. The whole idea is that you want to get randomness into the dataset, therefore you get small random variations from the given values. It is meant to be like that, since we try to simulate a sampling process.

    If you do it for a large sample (10 000) you get more accurate stdev and average.

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagDutch flagHindi flagPolish flagSwedish flagFilipino flagHebrew flagIndonesian flagUkrainian flagThai flagTurkish flag
treeemap software for excel