Replace =RAND() with =ABS(RAND())
Don
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
In article <BA3209B9-2B06-4D16...@microsoft.com>,
Charles Williams
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
"J.E. McGimpsey" <jemcg...@mvps.org> wrote in message
news:jemcgimpsey-BA6167.01161207122003@msnews.microsoft.com...
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
"Charles Williams" <Cha...@DecisionModels.com> wrote:
> 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
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" <jemcg...@mvps.org> wrote in message
news:jemcgimpsey-A8CD...@msnews.microsoft.com...
J.E. McGimpsey
"Charles Williams" <Cha...@DecisionModels.com> wrote:
> 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
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
Patrick O'Beirne
Don
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 - Mahalo
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>
--
HTH. Best wishes Harald
Followup to newsgroup only please.
J.E. McGimpsey
"Harald Staff" <inno...@enron.invalid> 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...
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.
--
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.
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
Harlan Grove<hrl...@aol.com> 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.
Charles Williams
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
"Harald Staff" <inno...@enron.invalid> wrote in message
news:%23xYkkVn...@TK2MSFTNGP09.phx.gbl...
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
"Charles Williams" <Cha...@DecisionModels.com> wrote in message
news:u5$GhEovD...@TK2MSFTNGP09.phx.gbl...
> 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
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" <hrl...@aol.com> wrote in message
news:GLnBb.34986$cJ5....@www.newsranger.com...
Bernard V Liengme
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" <S...@sysmod.com> wrote in message
news:3ec14cf6.03120...@posting.google.com...
James Silverton
"Bernard V Liengme" <blie...@stfx.TRUENORTHca> wrote in message
news:OPvDC00v...@TK2MSFTNGP09.phx.gbl...
> 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.
>
I presume and hope this refers to Excel 2003 since I am still using the
previous version even if the results of RAND() are not as well distributed
as they might be. There is no indication of trouble with RANDBETWEEN() in
that version.
I think Microsoft needs to take quick action like Intel did with the Pentium
bug many years ago. AFAIK, there were no reported financial losses due to
the Pentium bug but that might not be the case with RAND(). The ambulance
chasers and class actioners must be licking their lips!
--
James V. Silverton
Potomac, Maryland, USA
Harlan Grove
..
>I think Microsoft needs to take quick action like Intel did with the Pentium
>bug many years ago. AFAIK, there were no reported financial losses due to
>the Pentium bug but that might not be the case with RAND(). The ambulance
>chasers and class actioners must be licking their lips!
We can only hope.
David J. Braden
I didn't spot the problem earlier, but I was waiting for MS to specify the
algorithm before doing more than perfunctory testing. It has been a long
wait <g>. To your question, the seed for Rand() is apparently set by the
clock. Were you to restart your experiment, you would get a different
result. I do, for sure: under Win XP, I can't yet replicate what you found,
though I don't doubt it. I'm still plugging away on a fast machine.
Regards,
Dave B
"Bernard V Liengme" <blie...@stfx.TRUENORTHca> wrote in message
news:OPvDC00v...@TK2MSFTNGP09.phx.gbl...
James Silverton
> "James Silverton" wrote...
> ..
> >I think Microsoft needs to take quick action like Intel did with the
Pentium
> >bug many years ago. AFAIK, there were no reported financial losses due to
> >the Pentium bug but that might not be the case with RAND(). The ambulance
> >chasers and class actioners must be licking their lips!
>
> We can only hope.
>
>
As I recall in the case of the Pentium bug, Andy Grove sent a letter to me
personally apologizing and offering prompt free replacement (machine
produced probably, but still!). I wonder if we see this from Bill Gates!
Harlan Grove
..
>As I recall in the case of the Pentium bug, Andy Grove sent a letter to me
>personally apologizing and offering prompt free replacement (machine
>produced probably, but still!). I wonder if we see this from Bill Gates!
I doubt it. More likely he'll baracade himself behind the disclaimers in the
EULAs which basically say that anyone who uses any Microsoft software has no one
but himself/herself to blame if that software does anything, either desired or
undesired. If you read the EULAs rather than the marketing BS, Microsoft has
never claimed its software does anything useful.