Rand function

[Alexander Fischer]

Hello,

quite some time ago, I read some comments in this group about Excel's
RAND() function, saying that it was "awful". Is this true, and if so,
why?

Because of speed?
Or because it's not "really random"?

I'm not a mathematician, please use easy language on me

Thanks,

Al

 

[Guest]

Hi Al,

not sure what anyone else's gripes are with RAND, but I find it awkward for
the following:

* by default RAND() will only create a random number between 0 and 1 - in
order to have a different set of parameters, it's necessary to construct
(e.g.) using
RAND()*(b-a)+a to create a random between a and b - in which case
RANDBETWEEN(a,b) is easier to use if you want an integer return (example for
RAND taken from Excel Help).

* every time the worksheet is refreshed, the random number wil recalculate,
which means that any results based on that random number will change as well.
If the cell is activated, this will also prompt a change. If any other
formulae are entered anywhere else in the worksheet, this will prompt a
change. This is also true of RANDBETWEEN.

I've found that, for my purposes, I generally need to specify a random
number between two set parameters, be they fixed values, or a value which is
held in another range based on the formulae there. In these instances,
RANDBETWEEN has always worked better for me, followed by a copy/pastespecial
(values). However, RANDBETWEEN will only return integers, so if you require
decimals, you would need to get more advanced and include mathematical
functions.

e.g.

Range("A4").Select
Selection.Formula = "=RANDBETWEEN(0,$C$36)"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues

will return a value between 0 and the value in cell ref C36, then "fix" this
value so that it is held in cell A4 as a value as opposed to a continually
changing formulae.

Hope this helps
DS

 

[Mike Middleton]

Al -

There were many improvements in Excel 2003. For more info, see

Description of the RAND function in Excel 2003
http://support.microsoft.com/kb/828795

Description of improvements in the statistical functions in Excel 2003 and
in Excel 2004 for Mac
http://support.microsoft.com/default.aspx?kbid=828888&product=xl2003

- Mike
www.mikemiddleton.com

 

[Guest]

Oddly enough, the worksheet RAND function, ATP routines (including
RANDBETWEEN), and the VBA Rnd function all use different algorithms. None
were very random before Excel 2003. Only the worksheet RAND function was
improved in 2003. RANDBETWEEN may be more convenient, but should not be used
where serious randomness is required.

Jerry

 

[Alexander Fischer]

Hello all,
thanks for the comments so far.

Oddly enough, the worksheet RAND function, ATP routines (including
RANDBETWEEN), and the VBA Rnd function all use different algorithms. None
were very random before Excel 2003. Only the worksheet RAND function was
improved in 2003.

That's really interesting. Any way to access the worksheet RAND
function from within Excel, by some construction like Call
ExcelWorksheetFunction.RAND() or something similar?

RANDBETWEEN may be more convenient, but should not be used
where serious randomness is required.

I couldn't care less about convenience - I just want a truly random
double between 0 and 1

Al

 

[David J. Braden]

Alex,
I've been one (of many?) to have complained about Excel's rng's since Excel
2.0. To expand on/add to what Mike and Jerry pointed out, try this to see
just how lousy the ATP rng is:
Use ATH (Tools- Data Analysis...) to generate 10 000 uniform random variates
between 0 and 1 into a new ply. When it is done, the cells A1-A10000 will be
selected. Rename it (Insert-Name-Define) to ATPran, say.

1) Select B1-B10000, and array-enter (ctrl-shift-enter)
=FREQUENCY(ATPran,ATPran).

2) Now select A1-B10000, copy, and Paste Special Values to C1. Sort
C1-D10000 on Column D, descending.

You will then see which numbers were repeated, and how many times.

To take it a step further, you can repeat the process on your output. Select
D1 to the last cell in D that contains 1 (all the rest contain 0). Copy it,
and paste it to F1; name it ATPran2. Select from G1 to Gx, where x is the
last row in F with data, and array-enter =FREQUENCY(ATPran2,ATPran2) into
the cells.

Copy and Paste-special the values in F and G into H1, and sort descending on
column I. When I just did this, I got
1 7370
2 1108
3 122
4 12

(ignore the 0's)

In other words, 12 numbers in that run were repeated 4 times, 122 were
repeated 3 times, 1108 were repeated 2 times. Only 73.7% of the numbers were
unique. I've done this and at times had numbers repeated 6 times!!! Also,
ATP can generate both 0 and 1, which is odd, and a hassle to deal with in
many instances.

Moral: IMO, don't use the ATP random number generator for anything,
whatsoever. Microsoft has been alerted to this for *many* years,and has yet
to address it.

HTH
Dave Braden

 

[David A. Heiser]

Alex,
I've been one (of many?) to have complained about Excel's rng's since
Excel 2.0.

++++++++++++++++++++++++++++++++++++++++++++++++++++++
The addin random is a basic disaster. It should never be used. It is a 16
bit linear congruential generator with an extremely short period. It fails
all tests on random number generators. It is in a package that dates back to
a contractor that built the tool-pak. Apparently there are some contract
aspects such that Microsoft can't change the algorithms.

DAH

 

[Alexander Fischer]

Very interesting analysis. However, I had never used this add-in random
number generator anyway - either I use RAND() or VBA rnd().

For the RAND() function, your short test yields a highly satisfactory
result: 10000 unique numbers (XL 2003).
I did the same for VBA rnd(), and had the same result.

My takeaway: It seems to be safe to use RAND() or rnd(), even if I
didn't perform mathematical tests on true randomness (I know they exist
but couldn't apply them).

Thanks to all!
Al

 

[Guest]

I'm glad I came back to this one to see other peoples' takes on the random
functions. Very interesting reading, and my preference for RANDBETWEEN has
just been thrown out of the window.

Thanks for the heads up on these.