How to create a random number between a range?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dave - 03 Feb 2008 23:38 GMT

I need to generate random whole numbers between the range of 21 and 90.

I started with this:
=rand()*90
with the cells formatted to zero decimal places.

how do I limit the lower number?

Any help here will be appreciated.

Thanks in advance

dave

Reply to this Message

Tyro - 03 Feb 2008 23:51 GMT

=RAND()*(b-a)+a taken from the Excel help file. So in your case
RAND()*(90-21)+21. If you want integers only then =INT(RAND()*(90-21)+21
In Excel 2007, = RANDBETWEEN(21,90)

Tyro

>I need to generate random whole numbers between the range of 21 and 90.
>
[quoted text clipped - 8 lines]
> Thanks in advance
> dave

Reply to this Message

Tyro - 03 Feb 2008 23:53 GMT

Correction: (as usual)
If you want integers only then =INT(RAND()*(90-21))+21

Tyro

> =RAND()*(b-a)+a taken from the Excel help file. So in your case
> RAND()*(90-21)+21. If you want integers only then =INT(RAND()*(90-21)+21
[quoted text clipped - 14 lines]
>> Thanks in advance
>> dave

Reply to this Message

Stan Brown - 04 Feb 2008 03:12 GMT

Sun, 3 Feb 2008 15:53:22 -0800 from Tyro <Tyro@hotmail.com>:

> Correction: (as usual)
> If you want integers only then =INT(RAND()*(90-21))+21

That will give integers but from 21 to 89 not 21 to 90.

Signature

Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/
"If there's one thing I know, it's men. I ought to: it's
been my life work." -- Marie Dressler, in /Dinner at Eight/

Reply to this Message

Stan Brown - 04 Feb 2008 03:10 GMT

Sun, 3 Feb 2008 15:51:38 -0800 from Tyro <Tyro@hotmail.com>:

> =RAND()*(b-a)+a taken from the Excel help file. So in your case
> RAND()*(90-21)+21. If you want integers only then =INT(RAND()*(90-21)+21

I don't think this is correct, even leaving aside the matter of your
missing parenthesis. rand() returns a random real number
0 <= rand() < 1,
so
0 <= rand()*79 < 79
But int() truncates, so the whole numbers will be between 0 and 78,
not 0 and 79.

> In Excel 2007, = RANDBETWEEN(21,90)

It's not limited to Excel 2007; works just fine in Excel 2003.

Signature

Reply to this Message

Stan Brown - 04 Feb 2008 03:14 GMT

Sorry, 90-21 is 69 not 79! Corrected version:

Sun, 3 Feb 2008 15:51:38 -0800 from Tyro <Tyro@hotmail.com>:

> =RAND()*(b-a)+a taken from the Excel help file. So in your case
> RAND()*(90-21)+21. If you want integers only then =INT(RAND()*(90-21)+21

I don't think this is correct, even leaving aside the matter of your
missing parenthesis. rand() returns a random real number
    0 <= rand() < 1,
so
    0 <= rand()*(90-21) < 69
But int() truncates, so the whole numbers will be between 0 and 68,
not 0 and 69. And the given formula
    =INT(RAND()*(90-21)+21
will give values 21 to 89, not 21 to 90.

> In Excel 2007, = RANDBETWEEN(21,90)

It's not limited to Excel 2007; works just fine in Excel 2003.

Signature

Reply to this Message

Tyro - 04 Feb 2008 03:20 GMT

Stop bitching, the matter has been resolved. Why do you post so? After the
fact? Read the prior posts before you post. DUH!

Tyro

> Sorry, 90-21 is 69 not 79! Corrected version:
>
[quoted text clipped - 15 lines]
>
> It's not limited to Excel 2007; works just fine in Excel 2003.

Reply to this Message

Gord Dibben - 04 Feb 2008 01:12 GMT

The ATP function randbetween could be used

=randbetween(21,90) drag down 70 rows.

Unfortunately this can and will create duplicates.

See John McGimpsey's site for how to prevent duplicates.

http://www.mcgimpsey.com/excel/udfs/randint.html

Using RAND() in a column adjacent to a column with numbers 21 through 90 works
quite well.

Gord Dibben MS Excel MVP

>I need to generate random whole numbers between the range of 21 and 90.
>
[quoted text clipped - 9 lines]
>
>dave

Reply to this Message

Dave - 04 Feb 2008 01:20 GMT

Thanks for the replies.
I am in 2003 so I can use RANDBETWEEN(21,90) function.
However Tyro's solution works great and dups are not an issue in this case.

Thanks much

dave

> I need to generate random whole numbers between the range of 21 and 90.
>
[quoted text clipped - 8 lines]
> Thanks in advance
> dave

Reply to this Message

Stan Brown - 04 Feb 2008 03:18 GMT

Sun, 3 Feb 2008 17:20:36 -0800 from Dave <dave@accessdatapros>:

> However Tyro's solution works great and dups are not an issue in this case.

Better test it. You will get 21 to 89 not 21 to 90. (I put the
formula in 2500 cells, then did copy, Paste Special: Values, sort.
Not a single value was 90.)

Signature

Reply to this Message

Tyro - 04 Feb 2008 01:53 GMT

The solution of =INT(RAND()*(90-21)+21, taken from the Excel help file from
RAND()*(b-a)+a is wrong because RAND()*(b-a)+a never produces b. The reason
for that is the RAND function never produces 1 as a result. The RAND
function is usually described in most documentation as producing a value
between 0 and 1 actually produces a value between 0 and .999999999999999 as
the help file says. Therefore, it can never produce b. If I want a value
between 0 and 2, the formula RAND()*(2-0)+0 will never produce 2. The
maximum it can produce is 2 * .9999999999999999 = 1.99999999999999. So, for
you to get random values from 21through 90, inclusive, the formula needs to
be: =INT(RAND(91-21))+21. I didn't realize the Excel help was wrong until I
thought of the .9999999999 limitation on the return value from RAND().

Tyro

"Dave" <dave@accessdatapros> wrote in message
news:56FD14sss20-58DC-4385-A784-C9964269B8F3@microsoft.com...

>I need to generate random whole numbers between the range of 21 and 90.
>
[quoted text clipped - 8 lines]
> Thanks in advance
> dave

Reply to this Message

T. Valko - 04 Feb 2008 04:21 GMT

=INT(bottom+(top-bottom+1)*RAND())

Signature

Biff
Microsoft Excel MVP

> The solution of =INT(RAND()*(90-21)+21, taken from the Excel help file
> from RAND()*(b-a)+a is wrong because RAND()*(b-a)+a never produces b. The
[quoted text clipped - 25 lines]
>> Thanks in advance
>> dave

Reply to this Message

RagDyer - 05 Feb 2008 00:51 GMT

FWIW,

In actuality, it's the Int() function that causes the formula
[=RAND()*(b-a)+a ] to return an incorrect (upper limit will never be
returned) display.

Using Int(), the formula needs to be adjusted (as you've stated):

=Int(Rand()*(b-a+1)+a)

But, when *not* using Int(), this works accurately:

=Round(Rand()*(b-a)+a,0)

Simply put, the Int() function *truncates* the decimal, no matter how large,
while the Round() function allows the decimal to increment the value, where
appropriate, causing the return to display a value which is accurately
within the limits of the formula.

All I'm stating here is the accuracy of the *display*, not the actual
numerical value within the cell.

Signature

Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

Reply to this Message

Tyro - 05 Feb 2008 03:17 GMT

The Excel help file states:

"To generate a random real number between a and b, use: RAND()*(b-a)+a"

That will never produce b as an answer. If you want to compute random values
between 0 and 10, that is to say from 0 to 10 inclusive,
and enter =RAND()*(10 - 0) + 0 you will never see 10 because RAND always
returns a value less than 1. You have to work around
the help file formula with the INT function, or ROUND or whatever or
adjusting the value for b to compensate. The point is that the help
file statement is false.

Tyro

> FWIW,
>
[quoted text clipped - 46 lines]
>>> Thanks in advance
>>> dave

Reply to this Message

Stan Brown - 07 Feb 2008 10:15 GMT

Mon, 4 Feb 2008 19:17:09 -0800 from Tyro <Tyro@hotmail.com>:

> The Excel help file states:
>
> "To generate a random real number between a and b, use: RAND()*(b-a)+a"

But to get to that, you have to pass
"Returns an evenly distributed random number greater than or equal to
0 and less than 1."

That explains what is meant by "between" in that context. It means
greater than or equal to a and less than b, not less than or equal to
b.

Signature

Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com
A: Maybe because some people are too annoyed by top posting.
Q: Why do I not get an answer to my question(s)?
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?

Reply to this Message

Stan Brown - 04 Feb 2008 03:05 GMT

Sun, 3 Feb 2008 15:38:35 -0800 from Dave <dave@accessdatapros>:

> I need to generate random whole numbers between the range of 21 and 90.
>
> I started with this:
> =rand()*90
> with the cells formatted to zero decimal places.

=RANDBETWEEN(21,90) is by far the easiest way. (Make sure you have
Analysis Toolpak installed.)

If you want a whole number 21 to 90, and you limit yourself to rand
(), then you need =21+int(79.99999999*rand()) or something along
those lines.

Signature

Reply to this Message

Stan Brown - 04 Feb 2008 03:17 GMT

Sun, 3 Feb 2008 22:05:52 -0500 from Stan Brown
<the_stan_brown@fastmail.fm>:

> Sun, 3 Feb 2008 15:38:35 -0800 from Dave <dave@accessdatapros>:
> > I need to generate random whole numbers between the range of 21 and 90.
[quoted text clipped - 9 lines]
> (), then you need =21+int(79.99999999*rand()) or something along
> those lines.

That should be 70, not 79.99999999. Since rand() itself goes 0 to
not-quite-1, 70*rand() goes 0 to not-quite-70 and int(70*rand()) goes
0 to 69.

But =RANDBETWEEN() is definitely easier to read, and probably faster
too.

Signature

Reply to this Message