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

[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. Valko]

If you can download and install the free Morefunc.xll add-in from:

http://xcell05.free.fr/morefunc/english/index.htm

Alternative download site:

http://www.download.com/Morefunc/3000-2077_4-10423159.html

Then you can use a formula like this:

=TEXT(SETV(RANDBETWEEN(1,255)),"000 ")&CHAR(GETV())

The SETV function creates a static variable generated by the RANDBETWEEN
function and the GETV function retruns that static variable.

So, if RANDBETWEEN returns 76 GETV also returns 76.

 

[JoeU2004]

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