Solved

Random numbers in Excel

Posted on 2012-04-12
9
481 Views
Last Modified: 2012-05-14
Whats the easiest way to get a row of 10 random numbers? All 3 digits in length?
0
Comment
Question by:cansevin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 34

Assisted Solution

by:Norie
Norie earned 125 total points
ID: 37840586
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 81

Assisted Solution

by:byundt
byundt earned 125 total points
ID: 37840685
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 42

Assisted Solution

by:dlmille
dlmille earned 250 total points
ID: 37840826
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 37841098
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 34

Expert Comment

by:Norie
ID: 37841123
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 42

Expert Comment

by:dlmille
ID: 37841135
@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

by:Patrick Matthews
ID: 37841155
imnorie,

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

Patrick
0
 
LVL 42

Accepted Solution

by:
dlmille earned 250 total points
ID: 37841157
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 42

Expert Comment

by:dlmille
ID: 37841164
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question