Please see attached file.
My "calculated" polynomial is not the same as the Trend line on the graph. The one on the graph seems to be correct.
Basically, I am using the LINEST function to create a polynomial equation inorder to extrapolate data. And also with a given equation, and a given y, find x
Thanks for any help.
Mark
Last edited by mgaworecki; 10-23-2008 at 03:09 PM.
The simple answer is that you have numerical precision problems with floating point numbers when you're adding/subtracting numbers of wildly different sizes, as occurs both in LINEST and in computing a result from the coefficients. Courses in Numerical Analysis deal with this.
If you scale the x values (by 1000 for your example), things calm down. See attached.
Entia non sunt multiplicanda sine necessitate
Thank you, I sort of see the problem now. But I don't know exactly how to fix it. It does work fairly well when I adjust the x-values but what is the best way to do this if i want it to work for every situation (sometimes large numbers, sometimes small)? Should I have it compare the average differences between x&y and then if it is more than 10^2 times larger have it adjust it?
Thanks again
Mark
I am no expert in numerical analysis.
If you want to calculate a scale factor, maybe use the max of the absolute values of the raw data. Then the scaled x values are either
-1 to something < 1
or
something > -1 to 1
Calculate the regression based on the scaled x values, and then 'correct' the resulting scaled coefficients by dividing by the scale factor raised to the power of the term. E.g., if the coefficient for x^6 is 3.14 and the scale factor 72, then the corrected coefficient 3.14/72^6.
If you do it correctly, you'll see that the corrected cofficient don't change (unless you're looking at all significant digits) for a very reasonable range of scale factors.
Last edited by shg; 10-23-2008 at 03:04 PM.
Thank you very much, you've been a great help.
I have finally gotten the Linest Equation to match that of the Trendline but I can not get the result of that equation to come out correctly, any ideas?
Thanks again,
Mark
LINEST is not calculating the coefficients correctly for the reason I explained previously. See attached for the method to calculate coefficients from scaled x values, and then correct them for unscaled x values.
Last edited by shg; 10-24-2008 at 12:45 PM.
thanks again shg, I truely do appreciate your patience and help. I definatly understand it a lot better now. I was originally trying to figure out a way to do it without having to have the Analysis add-in but I guess that just won't work.
Mark
I played with it some more and found I was actually on the right track. But I had forgot to add the correct exponent to each term. While I do like your method and being able to choose the power, It did not work to well on my computer (some add-in troubles) so i figure it may not work well on others' computers.
thanks again for your help, I couldn't have done it without you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks