[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]

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

*From*:**Frank Stecher <Frank.Stecher@Sun.COM>***To*: office-formula@lists.oasis-open.org*Date*: Tue, 23 Jan 2007 16:39:29 +0100

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

S/MIME Cryptographic Signature

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]