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

[Guest]

I recommend you change every instance of =RAND() in your spreadsheets with =ABS(RAND()) as Excell 2003 gives values of =RAND() from -1 to +1.

Unless you want negative random numbers, that is...

Micros**t - Mahalo

Don.

 

[J.E. McGimpsey]

????

XL03 returns values in the range [0,1] (i.e, in the interval from 0
to 1, inclusive), just like all previous versions (though with a
better algorithm). See

http://support.microsoft.com/default.aspx?kbid=828795

 

[Charles Williams]

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.

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

????

XL03 returns values in the range [0,1] (i.e, in the interval from 0
to 1, inclusive), just like all previous versions (though with a
better algorithm). See

http://support.microsoft.com/default.aspx?kbid=828795

I recommend you change every instance of =RAND() in your spreadsheets with
=ABS(RAND()) as Excell 2003 gives values of =RAND() from -1 to +1.

Unless you want negative random numbers, that is...

 

[Harlan Grove]

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.

....

Oh swell! I wonder how many models will be silently fubarred due to this? At
least we can tell Microsoft's commitment to high quality software by the
time it'll take them to fix this.

How did something like this make it through beta testing?

 

[J.E. McGimpsey]

Unfortunately there does seem to be a bug in Excel 2003 Rand().

One more nail in BG's Trusted Computing coffin.

see Woody's Office Watch which says:

I must have missed it - which issue?

It only surfaces after a large number of calls to RAND().
Using ABS(RAND()) apparently biases towards zero.

I should think so...

Adding one if negative gives better results but is slow.

Is this confirmed as the source of the problem, or is it biasing
toward 1?

I verified the negative numbers on my system.

How many is "a large number"? For all the hoopla about MS's improved
algorthm, this should be truly embarrassing.

 

[Harlan Grove]

How many is "a large number"? For all the hoopla about MS's improved
algorthm, this should be truly embarrassing.

Especially since they could be using the BSD algorithm if they were willing
to include the Regents of the University of California copyright. MS had no
qualms about using BSD socket code.

 

[Charles Williams]

It was Woodys Office 2003 watch , not the standard Office watch, sorry.

I dont know how many is a large number: I created a few thousand =Rand() and
pressed F9 until negative numbers started appearing. Not sure if the bug is
caused by repeated F9s or just the cumulative number of RAND().

I cannot comment about the biasing, just reporting what Woody's said.

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

 

[J.E. McGimpsey]

It was Woodys Office 2003 watch , not the standard Office watch, sorry.

I dont know how many is a large number: I created a few thousand =Rand() and
pressed F9 until negative numbers started appearing. Not sure if the bug is
caused by repeated F9s or just the cumulative number of RAND().

I cannot comment about the biasing, just reporting what Woody's said.

I found the source, thanks. I was off myself on the biasing - I
thought the bug produced small negative numbers. According to the
article, it produces them through the whole [-1,0) range.

It will be interesting to see the response...

 

[Patrick O'Beirne]

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

 

[Guest]

How did it get through Beta Testing??

Well, they don't even read their own Community Web Site, because if you do a search you will find that I flagged this issue right here on Sept 28 2003..

.... Bernard V Liengme - still need proof

I had no other way to report it to them.. trust me I tried very hard, but got stuck in their Web Support Time warp... Contact Us...Blank Page... Contact Us... Blank Page.... Contact Us... AutoResponse from TechSup AutoBot.... Please Contact Us..

Micros??t - Mahal

Don

 

[Harald Staff]

(...) According to the
article, it produces them through the whole [-1,0) range.

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

 

[J.E. McGimpsey]

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...

 

[Harlan Grove]

...
...

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.

 

[Harlan Grove]

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?

 

[J.E. McGimpsey]

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.

 

[Charles Williams]

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/office2003/archtemplate.asp?1-n11


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

(...) According to the
article, it produces them through the whole [-1,0) range.

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

 

[Harlan Grove]

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/office2003/archtemplate.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?

 

[Harald Staff]

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

 

[David J. Braden]

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

 

[Bernard V Liengme]

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