Why use RAND when you have RANDBETWEEN?
Do you need a random number? Or lots of them? Then RAND and RANDBETWEEN are your functions.
Both are “volatile” functions: They produce a new random number each time the worksheet recalculates. Don’t want that? Then Copy and Paste Values is your friend.
Before we compare them, let’s make sure we understand both.
What is the RAND Function?
No, nothing to do with the South African currency. Though you might think from the way it fluctuates against the US dollar that the Rand’s value came from the RAND function!
RAND returns a random real number from 0 to 1 inclusive. It returns a new random number each time the worksheet recalculates.
Syntax:
RAND()
The function has no arguments.
Tip
What if you want to generate a random number but don’t want it to change each time the workbook recalculates? Type =RAND() in the formula bar. Without pressing Enter, press F9 to change the formula to a random number. Now press Enter. The formula will calculate and leave you with the value.
Example
Column C documents the formulas in column A. Column D describes the formula.
The RAND function is volatile. Your values will differ from those in the example above. Every time the worksheet recalculates, it generates new random numbers. This will happen when you enter a formula or data, or recalculate by pressing F9.
Introducing the RANDBETWEEN function
This function in Excel returns a random integer between the numbers you specify. RANDBETWEEN returns a new random number every time the worksheet recalculates.
Syntax
RANDBETWEEN(bottom, top)
Arguments:
Bottom (required). The smallest integer RANDBETWEEN will return.
Top (required). The largest integer RANDBETWEEN will return.
Examples
Column C documents the formulas in column A. Column D describes the formula.
Like RAND, the RANDBETWEEN function is volatile. Your values will differ from those in the example above. Every time the worksheet recalculates, it generates new random numbers. This will happen when you enter a formula or data, or recalculate by pressing F9.
Is RANDBETWEEN Better Than RAND?
It depends!
RAND only generates numbers between 0 and 1. If you want a different range, use a little maths. For example, to generate a random real number between a and b, use:
=a + (b-a)*RAND()
With RANDBETWEEN, on the other hand, you can specify the bottom and top values. That might be easier.
On the other, other hand, there is a pitfall to RANDBETWEEN –it only returns integers, rounded up for some reason. So if you mistakenly use =RANDBETWEEN(1.03, 1.12), to try to get random numbers between 1.03 and 1.12, you always get a result of 2!
If you specifically want integers, then RANDBETWEEN might be a better bet. Otherwise, you’d need something like
=INT(a + (b-a)*RAND() ) or
=ROUND(a + (b-a)*RAND(), 0)
Of course, you may want a specific number of decimal places. Then you have to either use RAND inside a ROUND function, or take the results of a RANDBETWEEN and divide. For example, divide by 1000 if you want three decimals.
That takes us back to having to use maths.
But then, we engineers are good at mathematics. Aren’t we?