Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Polynomial 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? -- Matt J |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Polynomial Treandline Coefficients
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. -- David "Matt J" wrote: 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? -- Matt J |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Polynomial Treandline Coefficients
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? -- Matt J "David" wrote: 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. -- David "Matt J" wrote: 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? -- Matt J |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Polynomial Treandline Coefficients
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 Matt J wrote: 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? -- Matt J "David" wrote: 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. -- David "Matt J" wrote: 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? -- Matt J |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Polynomial Treandline Coefficients
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. -- Matt J "S Davis" wrote: 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 Matt J wrote: 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? -- Matt J "David" wrote: 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. -- David "Matt J" wrote: 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? -- Matt J |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Polynomial Treandline Coefficients
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/micro...da30f29434786d Jerry "Matt J" wrote: 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? -- Matt J |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Polynomial Treandline Coefficients
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. -- Matt J "Jerry W. Lewis" wrote: 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/micro...da30f29434786d Jerry "Matt J" wrote: 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? -- Matt J |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to get coefficients for Polynomial regression as for rgp() | Excel Worksheet Functions | |||
Increaseing Precision in polynomial trendline equations | Charts and Charting in Excel | |||
How to put coefficients of trend line into spreadsheet? | Charts and Charting in Excel | |||
using linest to generate 3rd order polynomial coefficients | Excel Worksheet Functions | |||
Extracting Polynomial Coefficients | Excel Discussion (Misc queries) |