Excel Question

Factual Questions
1

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 < x < 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

2

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

3

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

4

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

5

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.

6

Very close. The suggestions above will give
(0<=x<=501)

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

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

7

Okay, you asked for it…

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? :slight_smile:

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

8

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.

9

Umm, i know you weren’t talking to me, mine works. :slight_smile:

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

10

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”

11

Please re-read my first sentance.

Then, please try rand() as a formula.

12

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

13

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