How to create a random number between a range?

[Dave]

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

 

[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
In Excel 2007, = RANDBETWEEN(21,90)

Tyro

 

[Tyro]

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

Tyro

 

[Gord Dibben]

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

 

[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

 

[Tyro]

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

 

[Stan Brown]

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]

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

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]

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

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

Sun, 3 Feb 2008 22:05:52 -0500 from Stan Brown

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

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.

 

[Stan Brown]

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

 

[Tyro]

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

Tyro

 

[T. Valko]

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

 

[RagDyer]

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.

 

[Tyro]

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,

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.
--
Regards,

RD

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

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

 

[Stan Brown]

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.

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