Excel’s RAND and RANDBETWEEN functions aren’t very well known, but they can be quite handy.
RAND Function
RAND is a simple function that returns a random number between 0 and 1.
It is entered =RAND()
That’s it…simple.
RANDBETWEEN Function
RANDBETWEEN is slightly more complicated.
=RANDBETWEEN(bottom,top)
Where ‘bottom’ is the lowest number you want returned and ‘top’ is the highest.
For example, to return a number from 1 to 100 you would enter:
=RANDBETWEEN(1,100)
If you prefer whole numbers then RANDBETWEEN is the best option.
Pressing F9 on your keyboard will calculate new random numbers, as will entering a new formula anywhere else in the workbook or any formatting changes like inserting columns/rows etc.
If you don’t want it to update just turn off automatic calculations:
Go to the Windows Button > Excel Options > Formulas section – see image below
Note: if you get an error when you use the RAND function in Excel you will need to install the Analysis ToolPack add-in first.
USES for RANDBETWEEN
RANDBETWEEN on its own has some limitations, for example you could end up with duplicate random numbers, which is no good if you want to randomly number a list.
But, team up RANDBETWEEN with the CHOOSE function and you’ve got a solution.
RANDBETWEEN and CHOOSE Example
Let’s say we have to select someone to work each Saturday…you’re not going to be the most liked boss, but if you can blame it on Excel’s Random Selection it might reduce some of the flack!
Below is a list of 7 employees and we have to choose one to work each Saturday.
Our formula is:
Use RAND with Microsoft Word!
Microsoft Word also had a RAND function which is handy when you’re setting up templates and you want to insert some text to experiment with layouts.
You enter it the same way you enter it in Excel. That is, simply type =RAND() into your document and press enter. Voila, you will be presented with 3 paragraphs of text.
If you want more or less than 3 paragraphs simply enter the number of paragraphs inside the brackets like this:
=RAND(5) This will give you 5 paragraphs of text.
{ 2 comments… read them below or add one }
Thanks for an idea, you sparked at thought from a angle I hadn’t given thoguht to yet. Now lets see if I can do something with it.
@mikeross. Great. I’d love to hear what you use RAND and RANDBETWEEN for your work.
Mynda.