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.
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.
- 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.
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.
- 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. 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
- Generate Random Number Between 0 and 1 in Excel (2 Methods)
- Random Number Generator with Data Analysis Tool and Functions in Excel
- How to Generate Random Data in Excel (9 Easy Methods)
- Random 5 Digit Number Generator in Excel (7 Examples)
- Random 4 Digit Number Generator in Excel (8 Examples)
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.
- 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?
Firstly, the RAND function generates random numbers.
The LN function returns the natural logarithm of the numbers returned by the RAND function.
The COS function returns the cosine of 2*PI()*RAND().
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.
- 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.
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.
- How to Generate Random 10 Digit Number in Excel (6 Methods)
- How to Generate Random Numbers Without Duplicates in Excel (7 Ways)
- Generate Random Number in Excel with Decimals (3 Methods)
- Generate Random Number between Two Numbers in Excel (4 Ways)
- Excel VBA: Random Number Generator with No Duplicates (4 Examples)
- Auto Generate Invoice Number in Excel (with 4 Quick Steps)