Excel Stat functions accuracy (All)

[pieterse]

Hi all,

Some time ago an article was posted about XL's inaccurate statistical functions.

Browsing around I bumped into this interesting article:

http://www.agresearch.cri.nz/Science/Stati...s/exceluse1.htm

[sdckapr]

Interesting article.

I guess it shows the point, that many teachers made to me in school to CHECK the results to make sure they makes sense.

Watch your sig figs, and beware of calcs that push the precision limit (large numbers with small differences between them). It might be better in some cases to "CODE" the original data and analyze the coded data to eliminate some of the things which causes these problems.

Also a point I have made on occasion, KNOW where your calcs come from and be sure it is appropriate function.

Steve

[HansV]

Steve,

That is very true; your remarks are valid even if you work with the big-shot statistical packages such as SPSS or SAS. They may be more accurate, and have better checks built in, but it is all too easy to use them to draw incorrect conclusions if you don't check whether the results make sense.

Still, it would have been nice if the Excel developers would have used less naive algorithms. I get the impression that they haven't put much effort into it after the original implementation, which is years ago by now. The standard deviation is a good example: I think it is standard practice to subtract the mean from the data before computing the SD, yet Excel bluntly uses the straight data.

[sdckapr]

I think they use the "faster way" for std dev.

Did you ever program or see programs to calc std dev? Go thru the dataset only ONCE. Sum X, SumX

[stuck]

If only people would take such advice! I handle (chemical) analytical results from all over the world and although I still smile at some of the numbers I see <img src=/S/eyeout.gif border=0 alt=eyeout width=15 height=15>, I am no longer surprised. It's scary <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>.

stuck

[pieterse]

Good points Steve.

I've heard that XL2003 will do better. MS is learning...

[sdckapr]

I am still amazed that many people will get data and IMMEDIATELY calculate corr coefficient or calc slope and intercept, without ever PLOTTING the data to see what kind of model you might want to fit. (many are correlated, but are not the simple y = mx + [img]/forums/images/smilies/cool.gif[/img].

Even the simple VISUAL clues of the regression line fit (or lack of fit) to the data is many times ignored.

For the most part, for all the complaints of the "accuracy" of the excel formulas and routines, I would lay odds that in the real world, the vast majority of users get the wrong answers thru their own ignorance and MISUSE of the formulas MUCH MUCH MUCH more frequently than get it from excel "miscalculating".

Steve

[HansV]

One of my best friends is a biostatistician; his "standard advice" to medical researchers is "Look at your data". A plot is an excellent means to get a feeling of how the data are laid out before doing any calculation.

[Howard Kaikow]

I sure hope so.
It would be hard to do worse.