Random Number Generator with Normal Distribution in Excel (4 Methods)

Often, we work with different kinds of data in Excel. We also perform data analysis. For that purpose, we may need to generate random numbers. We can handle that simple task by using the RAND function. However, real-life scenarios don’t contain evenly distributed values. For this reason, we need random numbers with Normal Distribution. In this article, we’ll show you the effective methods of the Random Number Generator with Normal Distribution in Excel.

To illustrate, we’ll use a sample dataset as an example. For instance, the following dataset specifies the Mean and Standard Deviation. We’ll thus generate 5 Random Numbers.

Introduction to Normal Distribution

The Normal Distribution is also known as a Bell Curve. The theory is based on the idea that the distribution of values mainly clusters around an average. Still, very high and very low values are possible in certain cases. But, they are rare compared to the ones closer to the average.

Random Number Generator with Normal Distribution in Excel: 4 Methods

1. Insert NORMINV Function for Random Number Generator with Normal Distribution in Excel

We know Excel provides many different functions. And we use them to perform numerous operations. The NORMINV function is one of such kind. It helps us to generate the inverse of the normal cumulative distribution. We have to specify the mean and the standard deviation in this function argument to perform the task. Therefore, follow the steps below to create the Random Number Generator with Normal Distribution in Excel.

STEPS:

• First, select cell D5. Here, type the formula:

• Then, press Enter.
• Eventually, use the AutoFill tool to generate 4 more numbers.

Here, B5 is the Mean and C5 is the Standard Deviation. We input the probability required in this function argument with the RAND function.

2. Create a Normally Distributed Random Number with Excel NORM.INV Function

Additionally, we can perform a similar task as stated in the previous method with the NORM.INV function. This function also returns the inverse of the normal cumulative distribution. But it is more compatible with the latest MS Excel versions. So, learn the following steps to carry out the operation.

STEPS:

• Firstly, select cell D5 to type the formula:

• Next, press Enter to return the value.
• At last, use AutoFill to fill the series.

B5 is the Mean and C5 is the Standard Deviation. We use the RAND function to input the probability required in this function argument.

3. Use the Box Muller Method for Random Number Generator with Normal Distribution in Excel

In this method, we’ll create a formula combining different Excel functions to apply the Box Muller method. Hence, follow the process to perform the task.

STEPS:

• First of all, select cell D5. Type the formula:

• After that, press Enter and use AutoFill to complete the rest.

🔎 How Does the Formula Work?

• RAND()

Firstly, the RAND function generates random numbers.

• LN(RAND()

The LN function returns the natural logarithm of the numbers returned by the RAND function.

• COS(2*PI()*RAND()

The COS function returns the cosine of 2*PI()*RAND().

• SQRT(-2*LN(RAND()))*COS(2*PI()*RAND())*$C$5+$B$5

Finally, the SQRT function returns random numbers.

4. Apply NORM.DIST Function to Create Random Number

In our last method, we’ll apply the NORM.DIST function for creating random numbers. The NORM.DIST function returns the normal distribution. But we have to specify the mean and the standard deviation. So, learn the following process to generate random numbers.

STEPS:

• Select cell E5 at first.
• Then, type the formula:

• After that, press Enter. Lastly, use AutoFill to return 4 more numbers.

Here, we generate the normally distributed random number for each mark in the B column.

Download Practice Workbook

Download the following workbook to practice by yourself.

Conclusion

Henceforth, you will be able to know the Random Number Generator with Normal Distribution in Excel using the above-described methods. Keep using them. Let us know if you have any more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.

<< Go Back to Random Number in Excel | Randomize in Excel | Learn Excel