Page created and updated by
Terry Sturtevant
Date Posted:
June 12, 2009
Here is a file with a simple linear least sqares fit data. There are
11 data points, and so using the LINEST function should give 9 degrees
of freedom. However, it automatically sets the slope to zero, and gives 10
degrees of freedom. However, if you multiply all of the x values by a
fixed amount, you can reach a point where the results will magically have
a non-zero slope and 9 degrees of freedom. In my sample spreadsheet below,
you should see that if the "multiplier" is 15 or less, the incorrect
answer is given, whereas if it's 16 or above, the answer is correct.
The example also shows the work-around for this: multiply your x
values by
some power of ten which makes the results correct, and then divide the
slope and standard error in the slope by the corresponding amount
afterwards.
I have not submitted a bug report to Microsoft because of their ridiculous
rules about submitting bugs:
How to submit a bug report
1. Point your Web browser to
http://connect.microsoft.com/feedback/default.aspx?SiteID=168 and sign in
with your Microsoft Passport Network credentials.
5. Click attach files and use the Browse tool to locate and attach your
Windows Live OneCare support log and Click Submit. Note that we cannot
accept bug submissions that don't include a support log.
Since I don't run Windows myself, I don't have either "Passport Network
Credentials" or a "Windows Live OneCare support log" (and have no desire
to, thanks.)
I guess that keeps the number of bug reports down.......
OpenOffice doesn't have this bug.
I set up the spreadsheet myself in OpenOffice and it wasn't until my
students started using Excel that the problem came up.
