Excel Tip: Generating Random Numbers

In last week's post about an online random name generator, I mentioned that you could use the RANDBETWEEN function if you wanted to generate random numbers instead.

I was also surprised to realise that I hadn't already written a post on this that I could link to - so I am remedying that this week.

The RANDBETWEEN function generates random whole numbers and is really simple to use. Its syntax is as follows:

=RANDBETWEEN(bottom,top)

where bottom and top are the lowest and highest numbers that you want to generate.

So, you could generate a random number between 1 and 10 using:

=RANDBETWEEN(1,10)

or between -100 and 100:

=RANDBETWEEN(-100,100)

or if you want to generate a decimal (2 decimal places) between 1 and 10, you could use:

=RANDBETWEEN(100,1000)/100

Note that the formula will recalculate (and therefore generate a new number) every time Excel calculates. With automatic calculation on, this will be every time you edit a cell on the sheet.

Often I use the function to generate a series of random numbers to use as sample data, in which case I will usually copy them and paste them as values over the formulae to fix the numbers.

You could also combine the function with INDEX to select random items from a list.

=INDEX(A1:A10,RANDBETWEEN(1,10))

will randomly return the contents of cells A1 to A10.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".