Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Extracting Polynomial Coefficients
Using Charts to ploy trend lines, there is an option to display a polynomial
curve fit of the data on the chart. Is there a way to extract the coefficients of the curve for further analysis in the spreadsheet? |
#2
|
|||
|
|||
Ken
not tried this but it seems you used LINEST function on the data to get what you need I found the MS help a bit un-helpful but this link may help http://www.stfx.ca/people/bliengme/E...Polynomial.htm also much more with a Google search you should be aware that the accuracy of LINEST does not always match that of the Excel curve fitting. Again see the google search for more detail. hth RES |
#3
|
|||
|
|||
The Excel help on polynomial fitting with LINEST is not very informative so your question is quite frequent. I have fought a lot with exactly the same problem AND solved it. Not very elegantly, but still it works and you get the coefficients in separate cells that are updated if input values change. I have put the solution into a small worksheet with two different ways of finding trendlines. If you dare display your e-mail address I can forward it. If not, just try this example where 13 sampled values are converted into a 3'rd degree polynomium: Put your sampled Y-values in column A from A3 to A15 Put your sampled X-values in column A from B3 to B15 In cell C3 write =B3^2 and in D3 write =B3^3 Copy the C3:D3 to C3:D15 In cell E3 write =LINEST(A3:A15;B3:D15) Mark cells E3 to H3 and press CTRL+SHIFT+ENTER (this enters an array formula), and your coefficients should appear. Check it with the trend on a graph of your sampled values The method should be able to create up 16'th degree polynomiums, but I haven't tried more than 6 and that works. Niels -- nsv ------------------------------------------------------------------------ nsv's Profile: http://www.excelforum.com/member.php...o&userid=26500 View this thread: http://www.excelforum.com/showthread...hreadid=401342 |
#4
|
|||
|
|||
correction Put your sampled X-values in column B from B3 to B15 -- nsv ------------------------------------------------------------------------ nsv's Profile: http://www.excelforum.com/member.php...o&userid=26500 View this thread: http://www.excelforum.com/showthread...hreadid=401342 |
#5
|
|||
|
|||
NSV and Ken
I suggest you both follow the link I posted earlier for a neater solution Using the example ranges Put your sampled Y-values in column A from A3 to A15 Put your sampled X-values in column A from B3 to B15 The function for a 3rd degree =LINEST(A3:A15,B3:B15^{1,2,3}) array entered into 4 cells (eg. A17:D17) A17 = 3rd power coefficient D17 is the intercept or Zero power coefficient This is all layed out with pictures at http://www.stfx.ca/people/bliengme/E...Polynomial.htm give it a go it can't hurt <g RES |
#6
|
|||
|
|||
You're right - a much more effective solution, but difficult to figure out. Wonder why they don't put it in the Excel HELP text. -- nsv ------------------------------------------------------------------------ nsv's Profile: http://www.excelforum.com/member.php...o&userid=26500 View this thread: http://www.excelforum.com/showthread...hreadid=401342 |
#7
|
|||
|
|||
The use of LINEST for this purpose has already been discussed.
Alternately, David Braden has posted VBA code to extract the coefficients directly from the chart into cells http://groups.google.com/groups?selm....microsoft.com The advantage of using the chart coefficients (either manually or via Braden's code) is that pre-2003 LINEST uses a numerically poor algorithm that can give inaccurate results with some data sets. The chart trendline (extracted by Braden's code) is much better numerically, provided you format the equation to display to full precision (scientific notation with 14 decimal places). Jerry Ken Hardman wrote: Using Charts to ploy trend lines, there is an option to display a polynomial curve fit of the data on the chart. Is there a way to extract the coefficients of the curve for further analysis in the spreadsheet? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Polynomial Formula Incorrect? | Charts and Charting in Excel | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
Trendline coefficients | Charts and Charting in Excel | |||
How do I reverse the linest coefficients? | Excel Worksheet Functions | |||
Code Post: Extract Trendline coefficients | Excel Discussion (Misc queries) |