Generate Random Number with Mean and Standard Deviation in Excel

In this tutorial, we are going to explain how to generate a random number with mean and standard deviation in Excel. However, you might want to generate a series of random numbers depending on a certain mean and standard deviation in various circumstances. Throughout this article, we’ll use different functions to perform this task properly.


Download Practice Workbook

You can download the practice workbook from here.


What Is Mean?

The average of a group of values is known as the mean. It refers to a data set’s values being distributed evenly. Generally, the mean measures the central tendency of a probability distribution along the median and mode in statistics.


What Is Standard Deviation?

Generally, the standard deviation in statistics measures the amount of variation or distribution in a set of numbers. If the value of standard deviation is low it means that the values are close to the mean value. On the other hand, if the value of standard deviation is high it means the values are distributed in a larger range.


4 Ways to Generate Random Number with Mean and Standard Deviation in Excel

In this article, we will show 4 ways to generate a random number with mean and standard deviation in Excel. So, to make you understand better we will illustrate the methods of this article with a unique dataset.


1. Insert NORM.INV and RAND Functions to Create Random Number in Excel with Mean and Standard Deviation

In this method, we will use the NORM.INV function to create a random number in excel with mean and standard deviation.

In Microsoft Excel the NORM.INV function returns the opposite of the normal cumulative distribution for the given mean and standard deviation.

In the following dataset, we have the values of Mean and Standard Deviation. Based on these values we will create random numbers in cells (B5:B10).

nsert NORM.INV and RAND Functions to Create Random Number in Excel with Mean and Standard Deviation

So let’s see the steps to perform this action.

STEPS:

  • To begin with, select cell B5.
  • In addition, type the following formula in that cell:
=NORM.INV(RAND(),$D$5,$E$5)
  • Press Enter.
  • Furthermore, drag the Fill Handle tool from cell B5 to B10.
  • Lastly, we get the result like the following image. We can see random numbers in cells (B5:B10) based on the value of mean and standard deviation.

NOTE:

Every time the worksheet is calculated or opened, the NORM.INV function method returns a new random number. So, you might not get the same result as our example dataset.

Read More: How to Calculate Mean Variance and Standard Deviation in Excel


 2. Apply NORM.DIST Function to Generate Random Number with Mean and Standard Deviation

In the second method, we will use the NORM.DIST function to generate a random number with mean and standard deviation.

In Excel the NORM.DIST function belongs to statistical functions. For a given set of conditions, it will calculate the normal probability density function or the cumulative normal distribution function.

We will get the output from the NORM.DIST function in the following dataset for the value of mean and standard deviation. Specify the value of indexes like the following image to get the output.

Go through the following steps to perform this method.

STEPS:

  • First, click on cell C5.
  • Next, insert the following formula in that cell:
=NORM.DIST(B5,$E$5,$F$5,TRUE)
  • Hit Enter.
  • Then, drag the Fill Handle tool from cell C5 to C10.
  • Finally, we can see random numbers generated in cells (C5:C10).

Apply NORM.DIST Function to Generate Random Number with Mean and Standard Deviation

Read More: Plot Normal Distribution in Excel with Mean and Standard Deviation


Similar Readings


3. Get Random Numbers with Mean and Standard Deviation Using RANDBETWEEN Function

In the third method, we will get a random number with mean and standard deviation using the RANDBETWEEN function.

The RANDBETWEEN function in excel generates a random number between two specific values.

In the following image of the dataset, we will create random numbers from the mean and standard deviation with the RANDBETWEEN function.

To do this follow the below steps.

STEPS:

  • Firstly, select cell B5.
  • Secondly, copy the following formula in that cell:
=RANDBETWEEN(($D$5-$E$5)*10,($D$5+$E$5)*10)/10
  • Press Enter.
  • Thirdly, to get the random numbers in other cells drag the Fill Handle tool from cell B5 to B10.
  • Lastly, we get the result like the following image.

Get Random Numbers with Mean and Standard Deviation Using RANDBETWEEN Function

Read More: How to Calculate Average and Standard Deviation in Excel


4. Use Box Muller Method for Creating Random Numbers with Mean and Standard Deviation in Excel

In the last method, we will use the Box Muller method for creating a random number with mean and standard deviation. To apply the Box Muller approach, we’ll develop a formula that combines various functions.

In the following dataset, we will create random numbers from the given value of mean and standard deviation. To do this we will apply the Box Muller method.

Let’s see the steps associated with this method.

STEPS:

  • In the beginning, select cell B5.
  • Next, write down the following formula in cell B5:
=SQRT(-2*LN(RAND()))*COS(2*PI()*RAND())*$E$5+$D$5
  • Hit Enter.
  • After that, drag the Fill Handle tool from cell B5 to B10 to generate all the random numbers.
  • In the end, we can see the result in the following image.

Use Box Muller Method for Creating Random Numbers with Mean and Standard Deviation in Excel

🔎 How Does the Formula Work?

  • RAND(): The RAND function generates random numbers.
  • LN(RAND(): The LN function converts the return value of the RAND function into a natural logarithm.
  • COS(2*PI()*RAND()): Returns the cosine value of 2*PI()*RAND().
  • SQRT(-2*LN(RAND()))*COS(2*PI()*RAND())*$E$5+$D$5: Here, the SQRT function returns random numbers.

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


Conclusion

In conclusion, this tutorial will show different ways to generate a random number with mean and standard deviation in Excel. Use the practice worksheet that comes with this article to put your skills to the test. If you have any questions, please leave a comment below. Our team will try our best to respond to you as quickly as possible. Keep an eye out for more creative Microsoft Excel solutions in the future.


Related Articles

Mukesh Dipto

Mukesh Dipto

Hi there! I am Mukesh Dipto. I graduated with a bachelor's degree in engineering. Currently, I am working as a technical content writer in ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include sports and meeting new people. I also enjoy sports. My favorite sports are football (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo