 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / November 2006Polynomial Treandline Coefficients
I used the "add trendline" in Excel 2000 to do a 3rd order poly curve fit of some data. Works great and I chose the "display equation" feature so I can see the resulting polynomial coefficients, BUT, how can I automatically use these coefficients in further calculations? Are they, can they be, available in cells? Used elsewhere, for a slope (derivative) function for instance?  SignatureMatt J Hi. I tried to do this once before also. I wanted to find the points on the trend line to work with them. I was trying with the 6th degree and the numbers got to large for Excel to do with, but maybe with the 3rd degree you can do it. You need to put the formula in a cell and actualy use the formula to find the data points. I was trying to find stock market closing and a derivative, but was not able to in the end. SignatureDavid > I used the "add trendline" in Excel 2000 to do a 3rd order poly curve fit of > some data. Works great and I chose the "display equation" feature so I can > see the resulting polynomial coefficients, BUT, how can I automatically use > these coefficients in further calculations? Are they, can they be, available > in cells? Used elsewhere, for a slope (derivative) function for instance? David, Sure, I can retype the polyonomial in a cell and then use the equation but what I was trying to do was automatically extract and use the coefficients in further calculations without having to retype them because the data that was curve fit was for one piece of a product that was going to be mass produced but may have slightly different data from each piece. Thanks anyway. Oter ideas anybody? SignatureMatt J > Hi. I tried to do this once before also. I wanted to find the points on the > trend line to work with them. I was trying with the 6th degree and the [quoted text clipped - 8 lines] > > these coefficients in further calculations? Are they, can they be, available > > in cells? Used elsewhere, for a slope (derivative) function for instance? Dont have anything in front of me right now, but Id imagine the help will show the necessary formulas within excel such as linest, etc. What was your original problem and how was your data set up? How do you know that in future you will always want a polynomial trendline and not a different, better fit estimate? That makes it a bit difficult to establish a catch all formula > David, > Sure, I can retype the polyonomial in a cell and then use the equation but [quoted text clipped - 20 lines] > > > these coefficients in further calculations? Are they, can they be, available > > > in cells? Used elsewhere, for a slope (derivative) function for instance? Each set of data will be fairly close to each other, within ~+/-10% or so. In order to demonstrate specification compliance I need to curve fit, calculate the slope and compare to a reference. The data set is only 10 points. I was hoping I could have Excel do this for me but I may have to write a macro or VB routine. SignatureMatt J > Dont have anything in front of me right now, but Id imagine the help > will show the necessary formulas within excel such as linest, etc. What [quoted text clipped - 26 lines] > > > > these coefficients in further calculations? Are they, can they be, available > > > > in cells? Used elsewhere, for a slope (derivative) function for instance? The simplest way is to use the TREND or LINEST worksheet functions. http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm shows how to specify polynomials to them. If you are overfitting the data, you may have numerical issues with versions prior to 2003. Beware in 2003 if a coefficient happens to be exactly zero. Alternately, Tushar Mehta has enhanced VBA code by David Braden to extract coefficients from the chart trendline. http://groups.google.com/group/microsoft.public.excel.charting/msg/0eda30f29434786d Jerry > I used the "add trendline" in Excel 2000 to do a 3rd order poly curve fit of > some data. Works great and I chose the "display equation" feature so I can > see the resulting polynomial coefficients, BUT, how can I automatically use > these coefficients in further calculations? Are they, can they be, available > in cells? Used elsewhere, for a slope (derivative) function for instance? Thanx!!!! the first approach using LINEST and entering the way you suggested in the first link works great. I wonder why this wasn't described in the excel help section. Thanks again. SignatureMatt J > The simplest way is to use the TREND or LINEST worksheet functions. > http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm [quoted text clipped - 13 lines] > > these coefficients in further calculations? Are they, can they be, available > > in cells? Used elsewhere, for a slope (derivative) function for instance? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.