How to generate random numbers in Microsoft Excel

0

If you need to generate one or more random numbers for business, educational, or other purposes, you can do so directly in Microsoft Excel. You can get random numbers using a random number generator tool or an Excel function.

RELATED: How to generate random numbers in Google Sheets

Use the random number generator in Excel

With an add-in from Microsoft called the Analysis ToolPak, you can perform many statistical and technical operations such as finding a moving average. Another feature of the tool is a random number generator.

Add the scan tool

To see if you already have the add-in, go to the Data tab and the Analysis section of the ribbon. Look for the Data Analysis button. If you have the button, you can switch to using the tool.

Go to Data, Data Analysis

If you don’t see the button, you can add it easily. Go to File> Options and select “Add-ins” on the left. At the bottom of the window, go to Manage and choose “Excel Add-ins”. Click on “Go”.

Select add-ins, manage Excel add-ins

When the Add-ins window opens, check the box next to Analysis ToolPak and click “OK”.

Add the scan tool

Use the random number generator

Head to the Data tab and click on “Data Analysis” in the Analysis section of the ribbon. When the window appears, select “Random Number Generation” and click “OK”.

Select random number generation

Starting at the top of the window, enter the number of columns you want to populate using the Number of variables box. Next, enter the number of rows in the Number of random numbers box.

Add the number of columns and rows

This generator is advanced in that you can select the distribution you want to use from options like Bernoulli, Binomial, Patterned, and Discrete. Once you have selected the distribution from the drop-down list, the Settings section will be updated with the necessary information to complete.

Choose a distribution

In the Random Seed field, you can enter a seed number (up to 9999) which the generator will use if desired. Then choose one of the output options for where you want your random numbers to appear.

Add a seed and choose the exit

Click “OK” when you are ready and you will receive your numbers.

Random numbers generated

Use random number functions in Excel

Another option to generate random numbers in Excel is to use a function. There are three functions you can use. Every time you recalculate or reopen the workbook, a new random number will be generated with these functions. They don’t offer as many options as the Random Number Generator tool, but they are simpler to use.

RELATED: How to find the function you need in Microsoft Excel

The RAND function

With the RAND function, you can generate a random number greater than or equal to zero and less than one. This gives you decimal number options. But you can also get numbers greater than one by changing the formula.

For a basic random number, type the following and press Enter:

=RAND()

RAND function in Excel

For a random number greater than or equal to zero and less than 500, type the following and press Enter:

=RAND()*500

RAND function for less than 500

For a random integer greater than or equal to zero and less than 500, type the following and press Enter:

=INT(RAND()*500)

Integer less than 500

The RANDBETWEEN function

Maybe you want to generate a number that falls between two specific numbers. In this case, you would use the RANDBETWEEN a function.

For a random number between 10 and 100, type the following and press Enter:

=RANDBETWEEN(10,100)

RANDBETWEEN positive numbers

For a random number between negative 10 and 10, type the following and press Enter:

=RANDBETWEEN(-10,10)

RANDBETWEEN with negative number

RELATED: How to create random (fake) datasets in Microsoft Excel

The RANDARRAY function

For Microsoft 365 subscribers, the RANDARRAY The function provides a set of random numbers. You can choose the number of rows and columns to fill with numbers. You can also select minimum and maximum values ​​and specify whole numbers or decimal numbers.

The syntax of this function is RANDARRAY(rows, columns, minimum, maximum, whole-decimal) where you enter True for a whole number or False for a decimal number as the last argument. All arguments are optional.

For a random array of numbers spanning three rows and four columns, you need to type the following and press Enter:

=RANDARRAY(3,4)

RANDARRAY with rows and columns

For a random table that covers the same number of rows and columns but also has a minimum of 1 and a maximum of 10, enter the following and press Enter:

=RANDARRAY(3,4,1,10)

RANDARRAY with minimum and maximum values

For a random array using these same arguments but only returning whole numbers, you must type the following and press Enter:

=RANDARRAY(3,4,1,10,TRUE)

RANDARRAY with whole numbers

Microsoft Excel gives you simple options for generating random numbers. Whether you need a discrete number or just need a number between one and 10, Excel has got you covered.

RELATED: How Computers Generate Random Numbers


Source link

Share.

About Author

Comments are closed.