[R] Problems with Excel (was SAS or R software)

[Patrick Burns]

I'm in the process of researching problems with Excel.  The references
given by Tim and Marc seem to lead to discussions of most of the problems
with statistical procedures in Excel.  The executive summary is that if
it is in Excel and it looks like statistics, then avoid it.

However, there are some other issues as well.  Here are three:

1)  For those of us used to S, Excel gives a non-intuitive result for:

           -2^4

I wonder how many formulas are in existence that have unintended results
due to this.

2)  When numbers in scientific notation are written to ascii (csv or 
txt), Excel
decides that some of the digits are unnecessary and doesn't write them 
to the
file.  As far as I can tell the number of significant digits that you 
get is arbitrary
and capricious.  Apparently Microsoft thinks of this as a feature -- I'm 
not sure
what they think the up-side is.  If you want all of your digits, make 
sure that none
of the numbers are displayed in scientific notation.  (Yes, what is 
written depends
on what is displayed, but it is not WYSIWYG.)

3)  In the olden days if there were a blank cell in a range of cells on 
which a
function operated (a mean perhaps), then Excel decided that the blank meant
zero.  Microsoft changed this so that blank cells are ignored 
(apparently a good
thing).  However, if you reference a blank cell, it is then counted as zero.

Exercise:  In the first column put numbers in the first few rows, but 
leave one
cell blank.  below that do the average of the cells (including the 
blank).  Now
in cell B1 put "=A1"  and copy this formula down column B.  You will get 
two
different means.


Patrick Burns

Burns Statistics
patrick at burns-stat.com
+44 (0)20 8525 0696
http://www.burns-stat.com
(home of S Poetry and "A Guide for the Unwilling S User")

Tim Churches wrote:

> Shawn Way wrote:
>
>> I've seen multiple comments about MS Excel's precision and accuracy.
>> Can you please point me in the right direction in locating information
>> about these?
>>  
>>
> As always, Google is your friend, but see for example 
> http://www.nwpho.org.uk/sadb/Poisson%20CI%20in%20spreadsheets.pdf
>
> Tim C
>
> ______________________________________________
> R-help at stat.math.ethz.ch mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide! 
> http://www.R-project.org/posting-guide.html
>
>
>