How to create a random number generator in Excel

How to use RANDBETWEEN to generate random numbers in Excel

If you want to generate random numbers within a specific range, the RANDBETWEEN function is a quick and easy way to do this. This allows you to generate random integers between any two values ​​of your choice.

Use RANDBETWEEN to generate random numbers in Excel:

1. Click the cell where you want the first random number to appear.
2. Type =RANDBETWEEN(1,500)Replace "1" with the lowest random number you want to generate and "500" with the highest random number.
   
3. Press Enter key, a random number within the range will be generated.
   
4. Click and hold the small square in the lower right corner of the cell, then drag down to fill as many cells as you want with the same formula.
   
5. These numbers are regenerated every time you make changes in Excel. If you want to keep them the same, copy and paste the formula as values.
   

How to use RAND to generate random numbers in Excel

You can also use the RAND function to get the same results, although if You want to generate integers, which requires more effort.

Use RAND to generate random numbers in Excel:

1. Click the cell where you want the first number to appear.
2. Type =RAND().
   
3. Press Enter key to generate a random decimal between 0 and 1.
   
4. If you want to convert it to an integer, change the formula to =ROUND(RAND()*100,0).
   
5. This will generate a random number between 1 and 100 by multiplying the decimal by 100 and rounding it to the nearest whole number. You can change "100" to any value you wish.
   
6. To create more random numbers in the same way, click and hold the small square in the lower right corner of the cell and drag it down.
   
7. Random numbers are recalculated every time a new formula is processed in Excel. To retain a specific set of values, copy and paste the formula as values.
   

How to Generate Unique Random Numbers in Excel

Because of the way RAND and RANDBETWEEN generate numbers, you may find that there are multiple times in your list of random numbers get the same value. This is to be expected; if the numbers are truly random, duplicate values ​​may appear if you generate enough values.

However, if you want to randomize a specific set of values, you can do so. For example, you might want to randomly arrange the numbers 1 through 20.

Generate unique random numbers in Excel:

1. Click the cell where you want to generate the first random number.
2. Type =RAND() and press Enter.
   
3. Click and hold the small square in the lower right corner of the cell.
   
4. Drag down to create 20 random values.
   
5. In the cell next to the first random value, type =RANK(B2, B$2:B$21)where "B" is the random value you created Column of values, "2" is the row of the first value, "21" is the row of the last value.
   
6. Press Enter key, a value between 1 and 20 will be generated.
7. Click and hold the square in the lower right corner of the cell and drag it down to create 20 values.
   
8. Excel will create a series of random numbers between 1 and 20 in random order.
9. In case you get repeated random decimals in the first row, type = in any empty cell and press Enter. The value will be regenerated.

Harness the Power of Excel

Learning how to create a random number generator in Excel is just one of the many ways you can use Excel for more than just storing tables of data.

The above is the detailed content of How to create a random number generator in Excel. For more information, please follow other related articles on the PHP Chinese website!