Solved

Generate a range of integer random numbers

Posted on 2013-02-05
8
334 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:teylyn
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:teylyn
teylyn earned 175 total points
ID: 38854261
You're right, Kris. My suggestion will never hit 122, it will always be below.
0
 
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 50

Expert Comment

by:teylyn
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
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 demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

706 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