• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 541
  • Last Modified:

Random numbers in Excel

Whats the easiest way to get a row of 10 random numbers? All 3 digits in length?
0
cansevin
Asked:
cansevin
  • 4
  • 2
  • 2
  • +1
4 Solutions
 
NorieVBA ExpertCommented:
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
 
byundtCommented:
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
 
dlmilleCommented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Patrick MatthewsCommented:
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
 
NorieVBA ExpertCommented:
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
 
dlmilleCommented:
@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
 
Patrick MatthewsCommented:
imnorie,

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

Patrick
0
 
dlmilleCommented:
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
 
dlmilleCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now