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