Random numbers in Excel

  • Thread starter Thread starter George
  • Start date Start date
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.
 
Typo alert.

Bob didn't mean to include the decimal point:

=randbetween(187343,765477)/100
 
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
 
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...
 
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
 
is this OK?
=rand()*(numup+numlow)-numlow
=rand()*(7654.77+1873.43)-1873.43

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