Random numbers in Excel

G

George

Hi to everyone
I would like to apply a general formula in Excel, in order to create random
(real and decimals) numbers in a cell range (lets say A1:A100) between
NUM_low – Num_up.

(Lets say NUM_low = - 1873,43

NUM_up. = 7654,77 )

P.S.

( The difficulty for me is how to manage the number of decimals 2,3,4 or more)


Thank you.
 
D

Dave Peterson

Typo alert.

Bob didn't mean to include the decimal point:

=randbetween(187343,765477)/100
 
R

RagDyeR

Also, it should be mentioned that it may be necessary for the ATP to be
activated/installed when using the Randbetween() function.

Since you mentioned a "General Formula" in XL:

The formula to return random numbers between two set-points is:

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

with "a" as the minimum limit,
and "b" as the maximum limit.

You also mentioned decimal places, so the above formula can be wrapped in
the Round() function, where the number of decimals to return is determined
by the last argument:

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

=Round(Rand()*(7654.77-1873.43)+1873.43,2)
=Round(Rand()*(7654.77-1873.43)+1873.43,3)
=Round(Rand()*(7654.77-1873.43)+1873.43,4)

--

HTH,

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


Typo alert.

Bob didn't mean to include the decimal point:

=randbetween(187343,765477)/100
 
G

George

Thank you all.

RagDyeR said:
Also, it should be mentioned that it may be necessary for the ATP to be
activated/installed when using the Randbetween() function.

Since you mentioned a "General Formula" in XL:

The formula to return random numbers between two set-points is:

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

with "a" as the minimum limit,
and "b" as the maximum limit.

You also mentioned decimal places, so the above formula can be wrapped in
the Round() function, where the number of decimals to return is determined
by the last argument:

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

=Round(Rand()*(7654.77-1873.43)+1873.43,2)
=Round(Rand()*(7654.77-1873.43)+1873.43,3)
=Round(Rand()*(7654.77-1873.43)+1873.43,4)

--

HTH,

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


Typo alert.

Bob didn't mean to include the decimal point:

=randbetween(187343,765477)/100
Click to expand...
 
B

Bernd P

Hello,

Sorry, no!

Rounding will give you a bias at the boundary values.

Take lower value 1 and upper value 2 and round to 1 digit, for
example. Do it on 10,000 runs. You will get a number between 1.0 and
1.1 about 500 times and you will get the number 2 for about 500 times.
All other intervals (1.1 - 1.2, 1.2 - 1.3, ...) will appear about
1,000 times.

I suggest to use:
=INT((RAND()*($B$1-$A$1)+$A$1)*10^2)/10^2
2 indicates the number of digits you want to get.

Regards,
Bernd
 
K

KC

is this OK?
=rand()*(numup+numlow)-numlow
=rand()*(7654.77+1873.43)-1873.43

decimals 2,3,4 or more, what is that please?
 
Top