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
Solved

Generate a range of integer random numbers

Posted on 2013-02-05
8
340 Views
Last Modified: 2013-02-05
What formula or combination of formulas can I insert into a large Excel table in order to generate a random integer number for each cell in the range of 65 to 122?  Duplicates expected.

(Using Excel 2003 where x = the number of cols and y = the number of rows)

Thanks in advance.
0
Comment
Question by:WaterStreet
8 Comments
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 38854239
Hello,

something along the lines of

=INT(RAND()*(122-65)+65)

cheers, teylyn
0
 
LVL 7

Accepted Solution

by:
leptonka earned 275 total points
ID: 38854251
Hi,

I think to have the correct intervall (including 65 and 122) you should add +1:
=INT(RAND()*(122-65+1)+65)

Cheers,
Kris
0
 
LVL 50

Assisted Solution

by:Ingeborg Hawighorst
Ingeborg Hawighorst earned 175 total points
ID: 38854261
You're right, Kris. My suggestion will never hit 122, it will always be below.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

 
LVL 24

Assisted Solution

by:Steve
Steve earned 50 total points
ID: 38854481
Can you not just use RANDBETWEEN

=RANDBETWEEN(65,122)

If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in.
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 38854493
... just in case the Analysis ToolPak is not available. For e.g. in my employer's network install it isn't.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38854746
No points, please :)

For Excel 2007+ users, RANDBETWEEN no longer requires the Analysis ToolPak.

I know this does not help WaterStreet right now...
0
 
LVL 18

Author Closing Comment

by:WaterStreet
ID: 38857259
Wow!  Thanks everyone.  

I'd be embarrassed to say how many hours I wasted trying to figure this out, until I got too tired and then remembered EE.

I have another question that is really the second half of my project's problem.  Don't answer it here, because I will post it as a new question in this Zone.  

If you're interested then think about how I would use the Accepted Solution to generate a table of integer random numbers in the specified range, like here, but where there are (let's say 3) Sub Ranges within -- that each have a specified weight/bias (Bias) for appearing in the table.  

I'll try to notify you here when I ask the new question.

Thanks
0
 
LVL 18

Author Comment

by:WaterStreet
ID: 38857856
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

839 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