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?
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.
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?
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.
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.
Dana DeLouis said:Another option might be to divide an integer by 10.
=RANDBETWEEN(0,100) / 10
For 0.0 to 10.0
= = = = = = =
HTH
Dana DeLouis
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.