Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Location
    Minneapolis, Minnesota, USA
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    t-test rounding problems (Excel 2000 SR-1)

    I frequently use the Excel t-test function at work as a quick screen of my data to get a rough idea of significance. For results that I submit, I use my company's own statistics package. This is fine most of the time, but it isn't constructed to handle large amounts of data well (it requires entering the means or the raw data for every test). When I have many tests to do, I would prefer to use Excel, since that's where the data is and I can simply cut-and-paste the formula many times. The problem with this approach is that Excel uses the raw data and does not hold the results its formula uses to significant digit conventions. The result is a p-value that differs from our statistics program because Excel doesn't round its calculations. The p-values are often different enough to be a concern, and thus I'm stuck using our stats program and entering the tests by hand. Is there any way to force Excel to hold to 2 (or any number) of significant digits when it performs calculations like a t-test? Does anyone have any suggestions? Thanks for your help.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: t-test rounding problems (Excel 2000 SR-1)

    If you are talking about using a specific number of places in the data you pass to the function, then you have a couple of options. You can use the Round function to put a copy of your data into a new row/column rounded to the number of places you specify, then pass that data to the function. Or, you could format the cells to display the number of places you want to use and then use Tools/Options/Calculations tab and select "Precision as displayed". This will round all values in the workbook to the number of places displayed in the cell. Be aware that any undisplayed precision will be lost.

    If you are talking about the precision used in the internal calculations of the function, I don't know of any way to control that. I think the functions will always use the maximum precision that the hardware can do.
    Legare Coleman

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: t-test rounding problems (Excel 2000 SR-1)

    Hi Alan,

    One way to deal with this is to code the T-test formula for yourself, rather than using Excel's built-in function. That way you can control the significant digit aspects of the calculations. Dig out the old elementary stats book and start from there ...

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

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

    Re: t-test rounding problems (Excel 2000 SR-1)

    Excel's statistical functions have been a concern regarding their incorrectness for about 10 years now.

    Read something about it here .
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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