Replace =RAND() with =ABS(RAND())

"J.E. McGimpsey" wrote in message
...
(...) According to the
article, it produces them through the whole [-1,0) range.

Top & bottom line then:
Replace =RAND() with =ABS(RAND())
bg

--
HTH. Best wishes Harald
Followup to newsgroup only please.

In article ,
"Harald Staff" wrote:

Top & bottom line then:
Replace =RAND() with =ABS(RAND())
bg

the one reason that a client of mine was considering going to XL03
for his company was for the improved stats calculations.

Even if this can be fixed quickly, the fallout (e.g., lack of
trust) has probably set that purchase back by a year or more...

"J.E. McGimpsey" wrote...
...
the one reason that a client of mine was considering going to XL03
for his company was for the improved stats calculations.

Even if this can be fixed quickly, the fallout (e.g., lack of
trust) has probably set that purchase back by a year or more...

Get VMWare for your client, and let them run gnumeric.

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.

"Charles Williams" wrote...
Unfortunately there does seem to be a bug in Excel 2003 Rand().

see Woody's Office Watch which says:

It only surfaces after a large number of calls to RAND().
Using ABS(RAND()) apparently biases towards zero.
Adding one if negative gives better results but is slow.

I verified the negative numbers on my system.
...

Without sarcasm this time - it's worse if negative values from RAND() don't
appear on the first call sometimes. If the odds of a negative result were 1/1000
or so, then every 1000th first call to RAND() should give a negative result. If
the negative values only occur after several thousand (?) calls in the same
session, it's far more likely there's a serious bug in RAND. It also raises the
question whether RAND() and ABS(RAND()) are still pseudouniform or even
symmetric.

Where's Dave Braden when we need him?

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.

In article ,
Harlan wrote:

Get VMWare for your client, and let them run gnumeric.

That might be a good option - he's already experimenting with
gnumeric under X11 on his Macs.

Replacing RAND() with ABS(RAND()) is not recommended.

Earl Takasaki is reported by Woody's to have tested the ABS(RAND()) approach
and found that it skews the distribution. A better bypass is apparently
adding 1 if negative.

see http://www.woodyswatch.com/office200...late.asp?1-n11


Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com

"Harald Staff" wrote in message
...
"J.E. McGimpsey" wrote in message
...
(...) According to the
article, it produces them through the whole [-1,0) range.

Top & bottom line then:
Replace =RAND() with =ABS(RAND())
bg

--
HTH. Best wishes Harald
Followup to newsgroup only please.

"Charles Williams" wrote...
Replacing RAND() with ABS(RAND()) is not recommended.

Earl Takasaki is reported by Woody's to have tested the ABS(RAND()) approach
and found that it skews the distribution. A better bypass is apparently
adding 1 if negative.

see http://www.woodyswatch.com/office200...late.asp?1-n11
...

Uh . . . if ABS(X) is skewed, so is 1+X. If you really need a mathematical
proof, here goes. If X has support only on (a,0), where a 0, then ABS(X) = -X.
For the same X, if ABS(X) is skewed, then so is -X, but -X is a linear
transormation of X, and so is 1+X. If one linear transformation of X is skewed,
all other linear transformations that don't involve multiplying X by zero are
also skewed.

So, 1+X may have looked better, but that appearance would be spurious.

So, I'll ast the question again: is the latest & greatest RAND() pseudouniform
or at least symmetric, or is is excrement masquerading as professionally
produced closed source intellectual property?

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.

"Charles Williams" wrote in message
...
Replacing RAND() with ABS(RAND()) is not recommended.

Hey, it was a joke...

Earl Takasaki is reported by Woody's to have tested the ABS(RAND()) approach
and found that it skews the distribution.

That's impossible. We are talking random numbers here.
.......ok, I'm leaving :-) .......

Best wishes Harald

I admit to having acquired a Windows platform a couple of weeks ago, and
trying to get all the insidious junk off of it that the vendor piled on
(some of it is part of the default OS install). That, and fixing an ailing
Mac, have thrown me off a bit.

To RAND():
In a perverse way, I'm sorta pleased that negative numbers showed up; it
seems to indicate that at least MS attempted 32-bit integers on this
go-round.

I missed the flag Don had raised re the generator, but had my own issues:
while RAND was touted at passing DIEHARD, it wasn't verifiable, since we
weren't given the underlying longs, or some way to map the doubles back to
them, in order to test the claim. Despite several requests, neither was the
generator documented, as per usual scientific standards.

Still, I have high hope the problem will be resolved, quickly. MS put a fair
amount of work into improving these functions; I would be surprised if they
were to let this undermine what otherwise is good (and
years-long-anticipated) work in other areas.

There's a well-documented, well-implemented, excellent alternative that is
*free* called PopTools: http://www.cse.csiro.au/poptools/
The author provides quite a few tools; a number are undocumented, but if you
think it should be there, it likely is. E.g., he provides a routine for
Cholesky decomposition if you want to generate multinormals; he also
provides a direct call, mentioned in his covar demo. I've been using it with
great success on a current project.

Check it out--- I think you'll like it. Again, it's documented!

Oh, one last question--- has anyone actually seen a 1, 0 or -1? I ran the
generator for a while during beta, never saw the negatives, nor 0 or 1, and
wasn't going to test it without further info from MS as to what they were
attempting to do. It's easy enough to avoid the endpoints (much less the
negatives bg).

DaveB

"Harlan Grove" wrote in message
...
"Charles Williams" wrote...
Unfortunately there does seem to be a bug in Excel 2003 Rand().

see Woody's Office Watch which says:

It only surfaces after a large number of calls to RAND().
Using ABS(RAND()) apparently biases towards zero.
Adding one if negative gives better results but is slow.

I verified the negative numbers on my system.
..

Without sarcasm this time - it's worse if negative values from RAND()
don't
appear on the first call sometimes. If the odds of a negative result were
1/1000
or so, then every 1000th first call to RAND() should give a negative
result. If
the negative values only occur after several thousand (?) calls in the
same
session, it's far more likely there's a serious bug in RAND. It also
raises the
question whether RAND() and ABS(RAND()) are still pseudouniform or even
symmetric.

Where's Dave Braden when we need him?

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.

Has anyone experimented with RANDBETWEEN? With RAND() one needs to do many
many recals to get neg values. But RANDBETWEEN(0,5) gave me lots of negative
values the first time I made a sheet with the formula repeated 500 times.

Also noticed another oddity with RAND(). With a block 2000 by 12 of =RAND()
formulas and a macro to repeatedly do recals, I found that when negative
values occur there are always 16.0 to 17.2% of them.

Bernard

"Patrick O'Beirne" wrote in message
om...
What a failure - imagine all the simulation models affected, never
mind the office lottery syndicates!

If anyone has stories of failures from this, the European Spreadsheet
Risks Interest Group would like to hear of them:

http://groups.yahoo.com/group/eusprig

www.eusprig.org

Patrick O'Beirne