help with RAND

S

shank

I have the following formula: =RAND()*(100-50)+50
How can I force it to return a whole number?
I'm creating worksheets for kids and I'm getting: 85+31=115
I have the cell attributes set to no decimals but I'm guessing the SUM
formula is still grabbing the full random numbers and rounding up or down.

While I'm on the subject, are there worksheets already created and available
for download somewhere. I'm teaching grandkids math skills.

thanks!
 
P

Paul

shank said:
I have the following formula: =RAND()*(100-50)+50
How can I force it to return a whole number?
I'm creating worksheets for kids and I'm getting: 85+31=115
I have the cell attributes set to no decimals but I'm guessing the SUM
formula is still grabbing the full random numbers and rounding up or down.

While I'm on the subject, are there worksheets already created and available
for download somewhere. I'm teaching grandkids math skills.

thanks!
Click to expand...

=ROUND(YourFormula,0)
will always return an integer. So:
=ROUND(RAND()*(100-50)+50,0)
 
B

Bob Phillips

Shank,

If you want a random number between 0 and y use

=INT(RAND()*(y+1))

If you want it to be between 1 and y use

=INT(RAND()*y)+1

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
H

Harlan Grove

...
...
=ROUND(YourFormula,0)
will always return an integer. So:
=ROUND(RAND()*(100-50)+50,0)
Click to expand...

Won't produce random integers from a discrete uniform distribution. The formula
above returns the following for the given value ranges returned by RAND().

0.00 < RAND() < 0.01 50
0.01 <= RAND() < 0.03 51
0.03 <= RAND() < 0.05 52
:
0.95 <= RAND() < 0.97 98
0.97 <= RAND() < 0.99 99
0.99 <= RAND() < 1.00 100

Note that 50 and 100 are each half as likely to result as any of the other
values. This may not matter to the OP, but random integers with discrete uniform
distribution between 50 and 100 inclusive are given by

=INT(50+(100-50+1)*RAND())
 
T

Tushar Mehta

[This followup was posted to microsoft.public.excel.misc with an email copy to shank.
Please use the newsgroup for further discussion.]

Interestingly and unfortunately, the only answer to your question was
by Harlan Grove, who buried it on the last line of his response to
someone else.

To get random numbers between a and b, both inclusive, where a < b, use
=INT(RAND()*(b-a+1))+a.

So, in your case, use =INT(RAND()*(100-50+1))+50.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Top