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

1. ## 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. ## 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.

3. ## 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

4. ## 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.