How can I use the same random number twice in the same formula?

B

Bob Arnett

I'm trying to use the same random number twice in the same cell formula:
I entered "=TEXT(RANDBETWEEN(1,255),"000 ")&CHAR(RANDBETWEEN(1,255))"
to display both an ASCII number and its character but this generates a
different random number for each "randbetween" instead of the same one. I
could use a helper column but I just wonder if this would somehow be possible
within the same cell.
 
T

T. Valko

J

JoeU2004

Bob Arnett said:
this generates a different random number for each "randbetween"
instead of the same one. I could use a helper column

If by "helper column", you mean copy-and-paste-special-value, that is the
only way I know of to "freeze" random values created using the RAND and
RANDBETWEEN functions. They are volatile functions; so by definition, they
change every time there is any change to the workbook :(.

but I just wonder if this would somehow be possible
within the same cell.

You can copy-and-paste-special-value back to the same cell(s). But then you
loose the formula, if/when you want to generate new random values.

Alternatively, I write my own VBA UDF; see below.

Technically, it is not exactly the same since Excel RAND returns a 64-bit
random value, whereas VBA Rnd returns a 32-bit random value. I don't know
which RANDBETWEEN uses. But the form of the random value (64-bit or 32-bit)
does not necessarily determine the "period" of the RNG (i.e. how long before
it repeats itself); and it probably does not matter unless you are
generating millions of random numbers in a single workbook.


Function myRandbetween(low As Double, high As Double) As Double
low = Int(low)
high = Int(high)
myRandbetween = low + Int((high - low + 1) * Rnd)
End Function
 

Ask a Question

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.

Ask a Question

Top