SP? Re: [R] Rounding problem R vs Excel

[david.whiting@ncl.ac.uk]

Hi again Sam,

Sorry for not replying sooner.  I have been ignoring everything for a
while to work on the AMMP analysis tool and the continuing saga of
migrating the legacy AMMP data.  Our consultant has written some
programs that take a VERY long time to run :(

Anyway, I now know Java and a certain amount of JSP.  

I hope to be able to get around to this soon.  It is all a bit hard
now with our data partially migrated.  Prod me again in the near
future if you haven't heard from me.

Dave


On Wed, Jun 04, 2003 at 04:54:26PM +0200, = Sam Clark = wrote:
> Wow!!  Not the first time MS products have done something 
> weird. Excel and Access and SQL Server all differ on how 
> they store dates internally, and this also leads to 
> problems - mainly in compatibility between them and their 
> date functions.  Did quite a bit of reading on it a while 
> ago but can't dig up references now.
> 
> David, I'm also wondering if you have a chance if you could 
> prep those data files I emailed you about a while ago.  I'd 
> like to work our manuscript into something submittable in 
> the next few months.
> 
> Thanks,
> 
> - S.
> 
> >Date: Wed, 4 Jun 2003 17:30:04 +0000
> >From: <david.whiting at ncl.ac.uk>  
> >Subject: [R] Rounding problem R vs Excel  
> >To: sam at samclark.net, p_setel at yahoo.com, Nigel Unwin 
> <n.c.unwin at ncl.ac.uk>, hmwanyika at yahoo.co.uk, Greg Kabadi 
> <greg.ammp at bigfoot.com>
> >
> >This discussion from the R-help mailing list might be of 
> interest to
> >you folks.  It was started when someone tried this in Excel:
> >
> >0.5 - 0.4 - 0.1
> >
> >and 
> >
> >(0.5 - 0.4 - 0.1)
> >
> >They give different results.  Try formatting the cells to 
> 20 decimal
> >places or multiplying the results in the next cells by
> >1,000,000,000,000,000,000
> >
> >Dave
> >
> >
> 20-OP
> >________________
> >Date: 04 Jun 2003 08:53:36 -0500
> >From: Marc Schwartz <MSchwartz at medanalytics.com>  
> >Subject: RE: [R] Rounding problem R vs Excel  
> >To: "Paul, David  A" <paulda at BATTELLE.ORG>
> >Cc: R-help at stat.math.ethz.ch, "'Duncan Murdoch'" 
> <dmurdoch at pair.com>
> >
> >On Wed, 2003-06-04 at 08:09, Paul, David A wrote:
> >> I don't have the reference, but a biologist friend of mine
> >> once showed me a refereed journal article that purported
> >> to demonstrate numerical errors made by MSExcel.  This 
> >> would have been Excel97 or Excel2000... In any case, the
> >> journal's scope was biological in nature and the article 
> >> was of interest since Excel is heavily used in that 
> community.
> >> 
> >> -david paul
> >
> >
> >There is a series of articles here:
> >
> >http://www.stat.uni-muenchen.de/~knuesel/elv/accuracy.html
> >
> >
> >In addition, there are additional references on Excel 
> specifically:
> >
> >
> >On the accuracy of statistical procedures in Microsoft 
> Excel 2000 and
> >Excel XP
> >B.D. McCullough and B. Wilson, (2002), Computational 
> Statistics & Data
> >Analysis, 40, pp 713 - 721
> >http://www.elsevier.com/gej-
> ng/10/15/38/85/51/28/abstract.html
> >
> >
> >On the accuracy of statistical procedures in Microsoft 
> Excel ???97
> >B.D. McCullough and B. Wilson, (1999), Computational 
> Statistics & Data
> >Analysis, 31, pp 27-37
> >http://www.elsevier.com/gej-ng/10/15/38/37/25/27/article.pdf
> >
> >
> >Problems with using Microsoft Excel for statistics
> >J.D. Cryer, (2001), presented at the Joint Statistical 
> Meetings,
> >American Statistical Association, 2001, Atlanta Georgia
> >at http://www.stat.uiowa.edu/~jcryer/JSMTalk2001.pdf
> >
> >
> >Use of Excel for statistical analysis
> >Neil Cox, (2000), AgResearch Ruakura
> >at 
> http://www.agresearch.cri.nz/Science/Statistics/exceluse1.htm
> >
> >
> >Using Excel for statistical data analysis
> >Eva Goldwater, (1999), Univ. of Massachusetts Office of 
> Information
> >Technology
> >http://www.umass.edu/acco/statistics/handout/excel.html
> >
> >
> >Statistical analysis using Microsoft Excel 
> >Jeffrey Simonoff, (2002)
> >at 
> http://www.stern.nyu.edu/~jsimonof/classes/1305/pdf/excelreg.
> pdf
> >
> >
> >Testing the Intrinsic Functions of Excel
> >National Physical Laboratory, UK
> >http://www.npl.co.uk/ssfm/ssfm1/validate/testing/excel.html
> >
> >
> >
> >There are also some general articles on several stats 
> applications by
> >McCullough.
> >
> >http://www.amstat.org/publications/tas/mccull-1.pdf
> >http://www.amstat.org/publications/tas/mccull.pdf
> >
> >
> >It has been some time since I looked at many of these 
> papers, but if my
> >memory is correct, in general, not much has changed in 
> Excel since "97".
> >However, from McCullough's most recent article:
> >
> >"The problems that rendered Excel 97 unfit for use as a 
> statistical
> >package have not been fixed in either Excel 2000 or Excel 
> 2002 (also
> >called "Excel XP"). Microsoft attempted to fix errors in 
> the standard
> >normal random number generator and the inverse normal 
> function, and in
> >the former case actually made the problem worse."
> >
> >
> >Many of the above articles have an overlap on references, 
> some
> >published, some are online resources or lecture notes.
> >
> >
> >HTH,
> >
> >Marc Schwartz
> >
> >______________________________________________
> >R-help at stat.math.ethz.ch mailing list
> >https://www.stat.math.ethz.ch/mailman/listinfo/r-help
>   sam at samclark.net
>   mobile: +27 (0)73 154-2069

-- 
Dave Whiting
Dar es Salaam, Tanzania