Need Help: LINEST function gives different Results in OOo,Excel and calculated manually

[Frank Stecher <Frank.Stecher@Sun.COM>]

Hi,

I'd like to start with a short statement about me and my work.

My name is Frank Stecher and I'm working as a Quality Assurance Engenier 
for Sun Microsystems Inc., StarOffice. I'm responsible for testing the 
Spreadsheet Component and the Digital Signature functionality for 
StarOffice and OpenOffice.org. I'm at Sun for 7 years now and enjoy the 
challenges given to me during my daily work. Especially  my function as 
the so called 'gatekeeper' for the OpenOffice.org  Calc Issues and the 
work on the OpenFormula Specification..
 
I need your help please. I've found some formulas describing the output 
for a normal LINEST function in OOo Calc and Excel perfectly well. The 
manually calculated values according to these formulas are the same as 
the ones calculated by Calc and Excel, so the formulas should be 
universal. The problem arises if you force the regression line through 
X=0 and Y=0.

In this case OOo Calc and Excel vary in it's results and the used 
formula to calculate it manually gives another result.

Please have a look at the attached files. The ODT shows the formulas 
used and the ODS is a document showing the results if opened with 
OpenOffice.org.

And these are the questions to be answered :

1.) The manual calculation and the calculation used in the LINEST 
function from OOo gives the same result but Excel is different. Who 
calculates it right? Are the formulas used for the manual calculation 
correct?

2.) What's about the calculation of the standard error of the slope 
(Sb), Calc and the manual calculation show the same result but Excel is 
different, who's correct ?

3.) I think it's Ok to set the standard error for the intercept output 
to #NA if I force a 0/0 Intercept as this Intercept is not calculated. 
What's your opinion here?

4.) R squared as a measurement unit for the fit of the line can't be as 
good as Excel states for a forced 0/0 intercept. OOo shows also the 
wrong result as it calculates it for the non forced line. So the 
question is who's right? Is it the manually calculated formula ?

5.) Is the formula used for the manual calculation of the standard error 
for Y (Sy) correct?  Excel and OOo gives us also a different result.

6.) The regression sum of squares (ssreg) is different for all three 
sources. Is the formula used for the manual calculation correct ?

For all questions above I would be interested to get corrected versions 
of the formulas if any has to be changed. Thanks for your help.

Frank

testdoc_linest_both_variants.ods

LINEST_definition.odt

S/MIME Cryptographic Signature