Solved

Random numbers in Excel

Posted on 2012-04-12
9
437 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
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 33

Assisted Solution

by:Norie
Norie earned 125 total points
Comment Utility
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

by:byundt
byundt earned 125 total points
Comment Utility
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

by:dlmille
dlmille earned 250 total points
Comment Utility
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

by:Patrick Matthews
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 33

Expert Comment

by:Norie
Comment Utility
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

by:dlmille
Comment Utility
@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
Comment Utility
imnorie,

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

Patrick
0
 
LVL 41

Accepted Solution

by:
dlmille earned 250 total points
Comment Utility
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

by:dlmille
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

763 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now