AllExperts > Excel 
Search      
Excel
Volunteer
Answers to thousands of questions
 Home · More Excel Questions · Answer Library  · Encyclopedia ·
More Excel Answers
Question Library

Ask a question about Excel
Volunteer
Experts of the Month
Expert Login

Awards

About Us
Tell friends
Link to Us
Disclaimer

 
 
 
 
About Jan Karel Pieterse
Expertise
Excel and Excel/VBA questions

Experience
Excel MVP

Organizations
Self employed Excel developer

Education/Credentials
Bachelor in Chemical Engineering

Awards and Honors
Microsoft MVP award since 2002

Past/Present Clients
Shell, Fortis bank, ABN-AMRO bank, Morgan Stanley, ...

 
   

You are here:  Experts > Computing/Technology > Microsoft Software > Excel > Linest fail

Excel - Linest fail


Expert: Jan Karel Pieterse - 11/4/2009

Question
I am using linest function to compute coefficients of a 4th order poly to fit a data set. I have used it successfully but today I came across a data set which produces faulty coefficients. When I checked it with a graph and a trend line of 4th order poly, it gives the coefficients correctly. What could go wrong?"


Answer
Hi Kaushalya,

I expect this is due to the nature of your data. The Y values are all within a close range with relatively little variation and the order of magnitude of X and X varies considerably. This makes the computation of the least squares error-prone.

You will probably benefit from normalising your data, e.g. by subtracting the average of x from the x's and of y from the Y's. Then do the 4th order regression using the transformed data.

Of course you'll have to do some math afterwards to get the true formula (and coefficients) back.

Add to this Answer   Ask a Question


 
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here
User Agreement | Privacy Policy | Kids' Privacy Policy | Help
Copyright  © 2008 About, Inc. AllExperts, AllExperts.com, and About.com are registered trademarks of About, Inc. All rights reserved.