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

=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

"Dave" <dave@accessdatapros> wrote in message
news:56FD1420-58DC-4385-A784-(E-Mail Removed)...
>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
>

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

Tyro

"Tyro" <(E-Mail Removed)> wrote in message
news:fsspj.10541$(E-Mail Removed)...
> =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
>
> "Dave" <dave@accessdatapros> wrote in message
> news:56FD1420-58DC-4385-A784-(E-Mail Removed)...
>>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
>>
>
>

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

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

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

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


"Dave" <dave@accessdatapros> wrote in message
news:56FD1420-58DC-4385-A784-(E-Mail Removed)...
> 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
>

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-(E-Mail Removed)...
>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
>

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.

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

Sun, 3 Feb 2008 15:51:38 -0800 from Tyro <(E-Mail Removed)>:
> =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.

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

Sun, 3 Feb 2008 15:53:22 -0800 from Tyro <(E-Mail Removed)>:
> 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.

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

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

Sun, 3 Feb 2008 15:51:38 -0800 from Tyro <(E-Mail Removed)>:
> =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.


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