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

Random Number Generator with Normal Distribution in Excel


Download Practice Workbook

Download the following workbook to practice by yourself.


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.


4 Methods of Random Number Generator with Normal Distribution in Excel

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 in order to perform the task. Therefore, follow the steps below to know the Random Number Generator with Normal Distribution in Excel.

STEPS:

  • First, select cell D5. Here, type the formula:
=NORMINV(RAND(),$B$5,$C$5)

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

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

Read More: Excel Formula to Generate Random Number (5 examples)


2. Create 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:
=NORM.INV(RAND(),$B$5,$C$5)
  • 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. And we use the RAND function to input the probability required in this function argument.

Read More: Generate Random Number with Mean and Standard Deviation in Excel


Similar Readings


3. Use 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:
=SQRT(-2*LN(RAND()))*COS(2*PI()*RAND())*$C$5+$B$5
  • After that, press Enter and use AutoFill to complete the rest.

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

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

Read More: How to Use Random Number Generator in Excel (5 Quick Ways)


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:
=NORM.DIST(B5,$C$5,$D$5,TRUE)
  • 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.


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.


Related Articles

Aung

Aung

I'm Aung. Recently I've earned my B.Sc. Degree in Electrical and Electronic Engineering. From now on, I will be working in Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo