Solved

# Random numbers in Excel

Posted on 2012-04-12
437 Views
Whats the easiest way to get a row of 10 random numbers? All 3 digits in length?
0
Question by:cansevin
• 4
• 2
• 2
• +1

LVL 33

Assisted Solution

Norie earned 125 total points
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.
0

LVL 80

Assisted Solution

byundt earned 125 total points
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.
0

LVL 41

Assisted Solution

dlmille earned 250 total points
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
0

LVL 92

Expert Comment

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
0

LVL 33

Expert Comment

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.
0

LVL 41

Expert Comment

@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
0

LVL 92

Expert Comment

imnorie,

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

Patrick
0

LVL 41

Accepted Solution

dlmille earned 250 total points
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
0

LVL 41

Expert Comment

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
0