# Random numbers in Excel

Whats the easiest way to get a row of 10 random numbers? All 3 digits in length?
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Analyst Assistant Commented:
You could use the RANDBETWEEN function, for example put this formula in A1 and copy across.

=RANDBETWEEN(100, 999)

It won't always generate 10 unique random numbers but the chances are quite good.

If you want values you can copy the range and use paste special.
Mechanical EngineerCommented:
If you want random whole numbers with no repeats, then try the following procedure:
1) Put the lowest possible number in cell A1, then next lowest in A2 and the third lowest in A3. For example, this might be 0, 1 and 2.
2) Select all three cells, then drag the little square at bottom right of the selection marquee down until you reach the largest possible number (e.g. 999)
3) In cell B1, put the following formula:
=RAND()
4) Select cell B1, then double-click the little square in bottom right of selection marquee. This copies the formula down to the end of column A.
5) Select both columns A and B, then sort by column B in ascending order

Your 10 unique random numbers will be in cells A1:A10. If you want more numbers (not duplicating the first selection), keep taking values from column A.

If you want another draw, then repeat step 5.
Commented:
As an alternative...

If you want to use VBA, the attached is a solution that will populate random numbers without repetition in a given range.

Usage:
Call genRand(Range("D1:G1"), 1, 8) 'would populate D1:G1 with random numbers between 1 and 8 without repeat.

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27442719.html

I can provide instructions for putting the function in a formula as well, as you wish (converting the SUB to a FUNCTION).  Then, the random numbers would keep regenerating if you used Application.Volatile as explained in the solution.  Warning, lots of use of this as a worksheet fucntion with Application.Volatile can be resource intensive, but if the random number generation using worksheet functions is not on a huge range, it should do ok.

Yes, I used a Go To , lol ;)

Cheers,

Dave
Commented:
imnorie,

As a point of reference, there is a roughly 4.8% chance that a sequence of 10 random integers between 100 and 999 will contain at least one duplicate value.  I'd say that that is way too high to simply place your trust in RANDBETWEEN.

:)

Patrick
Analyst Assistant Commented:
Patrick

Way too high for what?

Where in the question is random numbers with no duplicates mentioned?

PS I know that's probably what is wanted but...

PPs A set of true random numbers can have duplicates.
Commented:
@cansevin - as a follow-up to the vba solution I posted, in response to your question, the usage would be:

Call genRand(Range("A1:J1"), 100, 999) 'This should generate 10 unique random numbers in the range specified - re: 10 numbers on row 1.

I also added a function from: J.E. McGimpsey  http://www.mcgimpsey.com/excel/randint.html
in the worksheet, in case you want to use it as a worksheet function, rather than running a macro.

Usage - select the 10 columns on a row and type:

=randint(100,999)*(1+K3*0)

Then hit CTRL-SHIFT-ENTER to confirm it as an array function.  To make it update, you have to change the value in K3.

At this point they remain static - don't change after they are generated, unless you want them to - (as that was the goal in the solution I posted a link to in my first post), but instructions on the sheet advise how to update them.

All this can be simplified a bit if you really only want them to always be generating random numbers, but right now I left the static option in so you can have either option.

See attached,

Dave
genRandWStaticOpt-r2.xlsm
Commented:
imnorie,

You're right, the question makes no statement as to uniqueness.  My comment is hereby withdrawn :)

Patrick
Commented:
Sorry guys for this - trying not to spam, but was editing my last post:

Here goes - reposting (if any moderators out there, please delete my prior post and thanks!):

@cansevin - as a follow-up to the vba solution I posted, in response to your question, the usage would be:

Call genRand(Range("A1:J1"), 100, 999) 'This should generate 10 unique random numbers in the range specified - re: 10 numbers on row 1.

I also added a function from: J.E. McGimpsey  http://www.mcgimpsey.com/excel/randint.html
in the worksheet, in case you want to use it as a worksheet function, rather than running a macro.

Usage - select the 10 columns on a row and type:

=randint(100,999)*(1+K3*0) 'this function is specifically designed to deliver a static solution, where making a change to K3 forces the function to update.  One minor change to the formula and worksheet will make it dynamic.

Then hit CTRL-SHIFT-ENTER to confirm it as an array function.  To make it update, you have to change the value in K3.

At this point they remain static - don't change after they are generated, unless you want them to - (as that was the goal in the solution I posted a link to in my first post), but instructions on the sheet advise how to update them.

All this can be simplified a bit if you really only want them to always be generating random numbers, but right now I left the static option in so you can have either option.  A slight modification to the code and they can be randomly generating when your workbook calculates.

See attached,

Dave
genRandWStaticOpt-r2.xlsm

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
Independent of the original question, I think the chances of not having a duplicate in a set of 10 numbers with 900 potential values as being "quite good" was called into question.  One's tolerance for risk would be tested to accept the results, if indeed no duplicates were really needed.

>>PPs A set of true random numbers can have duplicates.

I think we all agree on that, but I've heard it said that if you can identify a set of random numbers as being random, then its not random.

Dave
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.