RAND function with categories

[benny]

Hi..it seems so simple but i couldn't make it.
What RAND function I should applied if I want to generate some randoms value
between 100 and 1000.

Many thanks and much appreciate for help.

 

[Dr. Stephan Kassanke]

Hi..it seems so simple but i couldn't make it.
What RAND function I should applied if I want to generate some randoms
value
between 100 and 1000.

Many thanks and much appreciate for help.

Hi Benny,

Rand() yields numbers between 0 and 1. You can transform this interval with
the following formula (abs cuts off the fractional part):

Abs(Rand() *900 +100)

or generally

Abs(Rand() * (UpperBound - LowerBound) + LowerBound)

cheers,
Stephan

 

[Bruno Campanini]

Hi Benny,

Rand() yields numbers between 0 and 1. You can transform this interval
with the following formula (abs cuts off the fractional part):

Abs(Rand() *900 +100)

or generally

Abs(Rand() * (UpperBound - LowerBound) + LowerBound)

cheers,
Stephan

Sorry Stephan, you said correct but you wrote wrong:

Int(Rand() * (UpperBound - LowerBound) + LowerBound)

Ciao
Bruno

 

[Dr. Stephan Kassanke]

Sorry Stephan, you said correct but you wrote wrong:

Int(Rand() * (UpperBound - LowerBound) + LowerBound)

Ciao
Bruno

so true, Int(...) is the way to go. Thanks for the correction. Just tried it
now and VBA does not like/know Rand(). It's called Rnd().

Stephan

 

[Dave Peterson]

I think you meant =Int(), not =abs().

=int(Rand()*900+100)

 

[Dr. Stephan Kassanke]

I think you meant =Int(), not =abs().

=int(Rand()*900+100)

mea culpa. Yor are absolutely correct.

cheers,
Stephan

 

[RagDyeR]

The formula to return random numbers between 2 set points is:

=RAND()*(b-a)+a

Where a = minimum limit
And b = maximum limit

So, for your question, use this formula:
=RAND()*900+100

To eliminate the decimals, you can Round() the formula:
=ROUND(RAND()*900+100,0)

The formula changes slightly if you truncate the decimal using Int():
=INT(RAND()*901+100)

This change insures that the maximum limit has the *possibility* of being
returned.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Hi..it seems so simple but i couldn't make it.
What RAND function I should applied if I want to generate some randoms value
between 100 and 1000.

Many thanks and much appreciate for help.

 

[Robert_Steel]

I have a follow up on the effect of INT and ROUND on the true randomness.
Stats is not a strength so please bear with me.
but please comment back

I am happy with the general formula as posted

=RAND()*(b-a)+a

Where a = minimum limit
And b = maximum limit

and have a gut feeling that the distribution of values remains as random
as the original RAND() function <g>

if you need integer values and use ROUND
eg. =ROUND(RAND()*(b-a)+a)
I think the probability of the numbers (a-1) through to (b+1) is equal
as they will be returned for numbers <+0.5 above AND >=0.5 below
ie the same span.
Howevere the possibility of returning a or b is approx half that of the
others as
a will be returned for numbers <=a AND >=(a-0.5)
b will be returned for numbers <(b+0.5) AND >=b
both are not precisely the same span but approx half that of all other
numbers

If I follow the same logic for the INT function with RagDyeR's modified formula
=INT(RAND()*(b+1-a)+a)
because as he states correctly

This change insures that the maximum limit has the *possibility* of being
returned.

I believe the probability of returning a particular integer is now equal
for all numbers in the range.
eg. range of values that return N is <(N+1) AND >=N

The other function that could be used but probably should be avoided is
TRUNC as this will give 'funky' results if the range crosses through zero.

In conclusion I believe the correct way to go is
=INT(RAND()*(b+1-a)+a)

Where a = minimum limit
And b = maximum limit

What do you all think?

cheers RES