Results 1 to 9 of 9
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Stat functions accuracy (All)

    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
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,158
    Thanks
    14
    Thanked 315 Times in 309 Posts

    Re: Excel Stat functions accuracy (All)

    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

  4. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 15 Times in 15 Posts

    Re: Excel Stat functions accuracy (All)

    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.

  5. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,158
    Thanks
    14
    Thanked 315 Times in 309 Posts

    Re: Excel Stat functions accuracy (All)

    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

  6. #5
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Stat functions accuracy (All)

    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

  7. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Stat functions accuracy (All)

    Good points Steve.

    I've heard that XL2003 will do better. MS is learning...
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,158
    Thanks
    14
    Thanked 315 Times in 309 Posts

    Re: Excel Stat functions accuracy (All)

    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

  9. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 15 Times in 15 Posts

    Re: Excel Stat functions accuracy (All)

    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.

  10. #9
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Stat functions accuracy (All)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •