How to Generate Random Numbers in Excel (Both 365 & 2016)

How to Generate Random Numbers in Excel (Both 365 & 2016)

Generating Random Numbers is a very important technique and is gaining popularity. It is one of the most used but often overlooked skill. Random numbers are required in various applications such as:

  1. Monte Carlo simulations
  2. Statistical sampling and analysis
  3. Sampling for machine learning and data analysis
  4. Testing and debugging of algorithms
  5. Gaming and gambling simulations
  6. Creating randomized data sets
  7. Cryptographic applications.

Now lets see some of the ways to use excel to generate random numbers. First we will look at the old fashioned ways and then more to a new recent technique released by Microsoft.

1) RAND function

Let’s see our function function which is RAND(). The excel function RAND() do not take any input argument and generate a random number between 0 and 1.

image

As you can see above, we have random numbers generated. Now if you want to get a different set of numbers from this, then the function will refresh every time you enter something in some other cells or press F9. The most likely distribution it will generate is a uniform distribution but since it is random, it may take either right skewed or left skewed distribution.

2) RANDBETWEEN function

Now let’s see our second function , RANDBETWEEN(botton, top) is used for generating integer random numbers. The function takes 2 arguments `bottom` which represents the least integer number to take and `top` represents the largest integer to take.

The generated random number will be

image 1
image 2

3) RANDARRAY function (Only for Excel 365)

Finally, we will see this amazing new function . This is an additional function in MS Excel 365 based on Dynamic Array engine. The function can take 5 arguments and all of them are optional. The following table shows each of them. I found this function interesting. Check out on Microsoft’s website.

RANDARRAY (rows, columns, min, max, integer)

ArgumentExplanation
rowsNumber of rows required
columnsNumber of columns required
minMinimum Number
maxMaximum Number
integerWhether Integer or Decimal Numbers If 0 then decimal numbers, if 1 then integer numbers
Table showing different types of arguments in RANDARRAY() function
image 3

As you can see that RANDARRAY() is a dynamic array function and generate spill over the cells.

To conclude I can say that although RANDARRAY() is a new function and is definitely a powerful one, but if you don’t have MS 365 then don’t worry, you can use the same function in Google Sheets. To check how to do it, follow this like. How to use RANDARRAY() in Google Sheets

Thats all for now. If you want to learn about programming in R language consider checking out this blog post.

Submit a Comment

Your email address will not be published. Required fields are marked *

academic Sidebar Image

Unlock the power of data with our user-friendly statistics calculator.

Explore now
academic Sidebar Image

Explore our data science courses to supercharge your career growth in the world of data and analytics.

REGISTER FOR COURSES
academic Sidebar Image

Test Your Skills With Our Quiz

SOLVE QUIZ

Contact me today! I have solution to all your problems.

Please enable JavaScript in your browser to complete this form.