This is specifically intended to fit a polynomial to scattered data, using a least squares method. Using the ALGLIB “PolynomialFit” fit routine.Using the function provided by Lori Miller in the comments: =MMULT(MMULT(TRANSPOSE(Y),X^N),MINVERSE(MMULT(TRANSPOSE(X^N),X^N))).Using the xy chart trend line for polynomials up to 6th order.Using the LINEST function: =LINEST(YRange, XRange^.It should be emphasised that high order polynomials are completely inappropriate for interpolating a function such as this it was chosen purely because it shows up the differences in the results from the four different methods examined. In order to investigate this I have looked at fitting polynomials of different degree to the function y = 1/(x – 4.99) over the range x = 5 to x = 6. Scott found that he was getting different results from Linest and the xy chart trend line for polynomials of order 5 and 6 (6th order being the highest that can be displayed with the trend line). When I enter an X value of 86 the calculated Y value not even close to the expected value of 10.5.This post is a follow up to Using LINEST for non-linear curve fitting and the following comments from Scott Rogers and Lori Miller.
When I enter an X value of 32 the calculated Y value is close to the original value of 276 but not exact. Let cell B8 be the input value for X (I labeled A8 as "Input X:") Now put the formula in the spreadsheet to solve for Y with any value of X Or perhaps this is clearer: y = -0.0013X^3 + 0.3459X^2 - 30.891X + 953.82Īnd the R-squared value is equal to 1 (My understanding is R squared of 1 means the data matches the trendline perfectly with the formula shown) The trendline is added and the equation for the trendline appears: TrendlineType - select Polynomial, Order value is 3.Ĭlick on the "Options" tab & check "Display equation on chart" & "Display R-squared value on chart" Right click a data point in the chart & Select "Add Trendline" Highlight cells A2:B5, select Insert>Chart (or use toolbar Chart button)Ĭhart Type: XY (Scatter), click Finish (or Next, Next, Next) Open the attachment and/or replicate the problem: I am using Excel 2003 but the same thing happened under a newer version (my son's Excel 2010, and apparently 2012) He also showed it to his professor (not sure what one) and (quote from email) "she was able to replicate the exact same problem we were having, and had no idea what was happening." This problem has stumped me (no surprise) & stumped my son (surprise, he is finishing his last year as a business major & has good experience with Excel). Excel calculated Polynomial formula does not match data, why?
0 kommentar(er)
