PDA

View Full Version : Excel Question

Jinx
04-03-2000, 07:33 AM
I never thought I'd be asking this because I used to think "random number generators" were just busy work for people learning how to program. But, now I need one!

Can an Excel Spreadsheet be programmed to randomly generate numbers within a desired range? If so, can you show me the code (macro, etc.) I should use to do just this?
Let's say I want the range to be from:
{0 &lt; x &lt; 501}

------------------
"They're coming to take me away ha-ha, ho-ho, hee-hee, to the funny farm where life is beautiful all the time... :)" - Napoleon IV

RussellM
04-03-2000, 08:00 AM
You may want to takes notes.

=RAND()*501

The RAND() function returns a random number greater than or equal to 0 and less than 1. Presumably, your installation of Excel does not have help installed?

Russell

RealityChuck
04-03-2000, 10:21 AM
If you want whole numbers within a range, you'll have have to use the ROUND function:

=ROUND(RAND()*501,0)

------------------
"What we have here is failure to communicate." -- Strother Martin, anticipating the Internet.

www.sff.net/people/rothman (http://www.sff.net/people/rothman)

ignatiusjreilly
04-03-2000, 03:19 PM
Jinx, if you have the statistical analysis add-in, you can use the RANDBETWEEN function.

RANDBETWEEN (X,Y)

Will provide a random integer within the range inclusive of X and Y.

If you're trying to take a ramdom sample from a given population, it's much more difficult - look for a shareware macro on your favorite FTP site.

billehunt
04-03-2000, 03:21 PM
Very close. The suggestions above will give
(0&lt;=x&lt;=501)

For (0&lt;x&lt;501), with rounding, i.e. random whole numbers between 1 and 500, use

=round(rand()*499+1,0)

Shagrath Borgir
04-03-2000, 08:33 PM

The code to do this in the VBA code (ie. to put the code behind a button or in a function) would be:

Randomize
x = Int(Rnd * y + 1)

Where y = the max number you want. X is the variable containing the results. Randomize is used to seed the rnd counter. Examples:

Randomize
x = Int(Rnd * 12 + 1)
msgbox x

Would display a message box with a random number between 1 and 12. That help at all? :)

------------------
"Through twilight, darkness and moonrise
My scarlet tears will run
As stolen blood and whispered love
Of fantasies undone"

Jinx
04-04-2000, 12:00 AM
It sounds odd, but I rarely think of the auto functions built into Excel. I'm used to the rigorous approach! You see, I'm still stuck working on the infinite series to determine a value for pi!

------------------
"They're coming to take me away ha-ha, ho-ho, hee-hee, to the funny farm where life is beautiful all the time... :)" - Napoleon IV

billehunt
04-04-2000, 12:52 AM
Hey, all the above uses of rand() and round() are wrong, including mine.

The definition of round(x) = int( x + 0.5 ). This is the approriate way to round numbers.

However, rounding is not what is appropriate here. That addition of 0.5 skews the first and last distributions (in the OP example, the odds of getting either 1 or 500 is one-half what it should be.)

The correct answer for a random integer between and including 1 and 500 is

=int(rand()*500+1)

I've got a sample spreadsheet that proves the above. If anyone is interested, I can try to find an FTP site to post it on, or post how to build it here.

Shagrath Borgir
04-04-2000, 01:53 AM
Umm, i know you weren't talking to me, mine works. :)

------------------
"Through twilight, darkness and moonrise
My scarlet tears will run
As stolen blood and whispered love
Of fantasies undone"

Shagrath Borgir
04-04-2000, 01:55 AM
Oh and btw, it's RND not RAND, at least in my code, and it works, i tested it.

------------------
"Through twilight, darkness and moonrise
My scarlet tears will run
As stolen blood and whispered love
Of fantasies undone"

billehunt
04-04-2000, 03:34 AM

Then, please try rand() as a formula.

Jinx
04-04-2000, 07:25 AM
This might sound like a stupid question, but:
Ok, assuming rand() works, for example... I plug in the formula and I get ONE solution. How do I get subsequent solutions? Do I have to keep telling Excel to "calculate now" to refresh the spreadsheet? Or, does the rand() function require a parameter telling it how many times to re-iterate?

As you can tell...I'm rusty!

------------------
"They're coming to take me away ha-ha, ho-ho, hee-hee, to the funny farm where life is beautiful all the time... :)" - Napoleon IV

RussellM
04-04-2000, 07:50 AM
Jinx - what is it you are trying to do?

If you are generating the random number in a cell, you could have the same formula in many cells or you could recalculate (f9) to get a new number.

If you are using VBA, you just run the same code again - FOR loop, DO loop, whatever

Tell us what you are trying to achieve and someone will be able to give you a splendid solution. If you just tell us how you are currently trying to solve this little bit, you will only ever get a small part of the solution, and it might not be the best one for your application.

Russell