Generating random numbers can be time-consuming if you have to do it manually. But this task comes up more often than you might think.
For example, have you ever needed to pull random numbers out of a hat to choose a contest winner? Or choose random numbers from a given number range to divide large groups into teams?
When you find yourself in such a situation, Microsoft Excel can make your work easier. It has multiple random number generator functions that can do the work for you.
In fact, Microsoft Excel is so useful for so many tasks that it “has become entrenched in business processes worldwide,” says business writer Eric Rosenberg. It’s a tool that benefits “any function in any industry” because it has “almost no limits for applications,” he writes.
Here’s how to generate random numbers in Excel.
The RAND function
The RAND function in Excel generates random real numbers greater than or equal to zero and less than one. The returned random numbers, other than zero, are decimal numbers. The formula operates without arguments or parameters. You can copy the formula =RAND() into as many cells as you need random numbers.
You can, however, use the RAND function with arguments. If you want to set an upper bound value higher than one, multiply the RAND function by that value. The formula is =RAND()*n where n is the value of the upper bound.
Say you want a real number between zero and 40. The formula would be =RAND()*40. Note that the upper bound value is never returned in the results, so if you want the value 40 to be a possible returned value, the formula would need to be =RAND()*41.
You can also generate a random number between two numbers. The formula is =RAND()*(b−a)+a. If you want a number between 20 and 60 — where a is the lower bound value and b is the upper bound value — the formula would be =RAND()*(60−20)+20. Note that this formula will also not return a value equal to that of the upper bound value.
While this function in Excel can certainly save you some time, it does have one potential drawback. It generates a new random number every time an action is performed in a worksheet — even if it has nothing to do with the RAND formula — which could cause confusion. For example, any time you edit any cell or enter new data anywhere on the worksheet, the RAND formula will generate a new number.
You can, however, override this by entering =RAND() in the formula bar of a cell and then pressing F9 to change the formula to a random number. The formula will calculate and return the value just once.
You can also generate only whole numbers (or integers) using the RAND function. To do this, encapsulate either of the above formulas inside the INT function, which looks like this: =INT(RAND()*n). So, to return a random whole number between zero and 60, use =INT(RAND()*60). Or, to generate random whole numbers between 20 and 60, the formula would be =INT(RAND()*(60−20)+20).
With any of these formulas, you can generate random numbers in Excel. There is also a simpler formula for generating random integers — the RANDBETWEEN function.
The RANDBETWEEN function
When you’re learning how to generate random numbers in Excel, the RANDBETWEEN function is an important one to master. While you can use the RAND function to achieve the same results, RANDBETWEEN is a simpler formula to return whole numbers and those within a specific range.
The formula for this function is =RANDBETWEEN(bottom,top) — the bottom number is the lowest in the range, and the top is the highest in the range. One unique feature of this function is that it can return both negative and positive numbers.
So, if you want to return a whole number between 20 and 60, the formula would read =RANDBETWEEN(20,60). Or, if you want to generate numbers between -20 and 20, you would plug =RANDBETWEEN(-20,20) into the formula bar.
As with the RAND function, the RANDBETWEEN function will automatically recalculate every time you make any changes anywhere in your spreadsheet. To sidestep this potential complication, enter the formula, then press F9 to change the value to an integer as opposed to the formula.
Microsoft Excel exists “to make your life easier,” says business consultant Isaac Jeffries. One of the ways it does this is by giving you opportunities to generate as many random numbers as you could possibly need. Learning how to generate random numbers in Excel is relatively simple, and the knowledge will save you a lot of time on mundane tasks.
Send Comment: