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

[Kjetil Brinchmann Halvorsen]

Patrick Burns wrote:

> 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

Yes. However, OO Calc gives the same result as Excel (Office 2003). Are 
they trying to
heavily to mimic Excel?

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

Tried this to with OO Calc. All were written with 14 significant digits. 
However, test was very
fast and needs replcation investing some more time.

>
> 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.
>
Yes. Done, but also on OP Calc with *exactly* the same result. 

Does one need to investigate OO Calc in the same way as has been done
with Excel?

Kjetil

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


-- 

Kjetil Halvorsen.

Peace is the most effective weapon of mass construction.
               --  Mahdi Elmandjra