RAND and only 1 decimal

  • Thread starter Thread starter SAC
  • Start date Start date
S

SAC

I want to make a random number in a cell with the RAND function and then use
it in a calculation. Right now it goes to several decimal place and I need
it to only have one decimal in the calculation.

How can I do this?

Thanks for your help.
 
SAC said:
I want to make a random number in a cell with the
RAND function and then use it in a calculation.
Right now it goes to several decimal place and I
need it to only have one decimal in the calculation.
How can I do this?
Click to expand...

Ostensibly, ROUNDDOWN(RAND(),1). However, that might not be the right thing
to for your purposes.

That would be the right thing to do if you are trying to select a random
factor from the following list of ten: 0.0, 0.1, 0.2,..., 0.9.

(Note that I used ROUNDDOWN, not ROUND. That preserves the expectation that
RAND returns a result from and including zero up to but excluding one. That
expectation is often critical to the use of RAND in most formulas. If you
use ROUND instead, you are effectively selecting from a list of 11, to wit:
0.0, 0.1, 0.2,..., 1.0.)

But I wonder if you are really using RAND() as a factor in some larger
expression, and you wish the result of the entire expression to be limited
to one decimal place. In that case, you probably want to round (or round
down) the result of the entire expression. For example:

=ROUND(A1 + A2*RAND(), 1)

or at least

=A1 + ROUND(A2*RAND(),1)

The timing of when to round (or round down) can have a major impact on the
outcome, depending the magnitude of numbers. Consider the example where A2
is 33 and RAND() returns about 0.15.

1. A2*ROUND(RAND(),1) is 6.6.
2. A2*ROUNDDOWN(RAND(),1) is 3.3.
3. ROUND(A2*RAND(),1) is 5.0.

The point is: you will get a more robust range of A2*RAND() when you delay
rounding as opposed to rounding (down) RAND first.
 
Another option might be to divide an integer by 10.

=RANDBETWEEN(0,100) / 10

For 0.0 to 10.0

= = = = = = =
HTH
Dana DeLouis
 
=ROUND(RAND(),1)

That could possibly return a result of 1 if RAND() = something like
0.9999999999999999.

Maybe something like this:

=TRUNC(RAND(),1)

But that can also return a result of 0. Is 0 a valid number for your
application?
 
I said:
Consider the example where A2 is 33 and RAND()
returns about 0.15. [....]
The point is: you will get a more robust range
of A2*RAND() when you delay rounding as opposed
to rounding (down) RAND first.
Click to expand...

To clarify, if you do A2*ROUND(RAND(),1), you are limiting the result of the
entire expression to 0, 3.3, 6.6, 9.9, 13.2, 16.5, 19.8, 23.1, 26.4, 29.7
and 33.

With ROUND(A2*RAND(),1), the result will be any of 331 values from 0.0, 0.1,
0.2,..., 32.8, 32.9 and 33.0. That is usually what we want.


----- original message -----
 
Thanks!
T. Valko said:
That could possibly return a result of 1 if RAND() = something like
0.9999999999999999.

Maybe something like this:

=TRUNC(RAND(),1)

But that can also return a result of 0. Is 0 a valid number for your
application?
Click to expand...
 
Wow! Thanks, Joe.

Joe User said:
I said:
Consider the example where A2 is 33 and RAND()
returns about 0.15. [....]
The point is: you will get a more robust range
of A2*RAND() when you delay rounding as opposed
to rounding (down) RAND first.
Click to expand...

To clarify, if you do A2*ROUND(RAND(),1), you are limiting the result of
the entire expression to 0, 3.3, 6.6, 9.9, 13.2, 16.5, 19.8, 23.1, 26.4,
29.7 and 33.

With ROUND(A2*RAND(),1), the result will be any of 331 values from 0.0,
0.1, 0.2,..., 32.8, 32.9 and 33.0. That is usually what we want.


----- original message -----

Joe User said:
Ostensibly, ROUNDDOWN(RAND(),1). However, that might not be the right
thing to for your purposes.

That would be the right thing to do if you are trying to select a random
factor from the following list of ten: 0.0, 0.1, 0.2,..., 0.9.

(Note that I used ROUNDDOWN, not ROUND. That preserves the expectation
that RAND returns a result from and including zero up to but excluding
one. That expectation is often critical to the use of RAND in most
formulas. If you use ROUND instead, you are effectively selecting from a
list of 11, to wit: 0.0, 0.1, 0.2,..., 1.0.)

But I wonder if you are really using RAND() as a factor in some larger
expression, and you wish the result of the entire expression to be
limited to one decimal place. In that case, you probably want to round
(or round down) the result of the entire expression. For example:

=ROUND(A1 + A2*RAND(), 1)

or at least

=A1 + ROUND(A2*RAND(),1)

The timing of when to round (or round down) can have a major impact on
the outcome, depending the magnitude of numbers. Consider the example
where A2 is 33 and RAND() returns about 0.15.

1. A2*ROUND(RAND(),1) is 6.6.
2. A2*ROUNDDOWN(RAND(),1) is 3.3.
3. ROUND(A2*RAND(),1) is 5.0.

The point is: you will get a more robust range of A2*RAND() when you
delay rounding as opposed to rounding (down) RAND first.
Click to expand...
Click to expand...
 
Back
Top