2nd Order Polynomial Curve-fit using Trend function

%3CLINGO-SUB%20id%3D%22lingo-sub-375666%22%20slang%3D%22en-US%22%3E2nd%20Order%20Polynomial%20Curve-fit%20using%20Trend%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-375666%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20small%204-pair%20(x%2Cy)%20data%20set%20that%20I'm%20trying%20to%20extrapolate%20using%20a%202nd%20order%20curve-fit.%26nbsp%3B%20I%20have%20the%26nbsp%3B%20desired%20value%20of%20%22y%22%20and%20I%20want%20the%20corresponding%20%22x%22%2C%20which%20may%20be%20a%20negative%20number.%26nbsp%3B%20If%20I%20plot%20the%20data%20in%20an%20Excel%20chart%2C%20and%20%22forecast%22%20the%202nd%20order%20trendline%2C%20I%20get%20a%20reasonable%20answer.%26nbsp%3B%20If%20I%20use%20the%20LINEST%20function%2C%20calculate%20the%20polynomial%20coefficients%20and%20solve%20the%20quadratic%20equation%2C%20I%20get%20a%20reasonable%20answer%20if%20I%20control%20the%20(%2B%2F-)%20option%20on%20the%20quantity%20%2B%2F-SQRT(B%5E2-4AC).%26nbsp%3B%20There's%20two%20real%20solutions%20(roots)%20to%20the%20quadratic.%26nbsp%3B%20Using%20the%20-SQRT(B%5E2-4AC)%20option%2C%20yields%20a%20result%20which%20is%20compatible%20with%20the%20trendline%20forecast%20on%20the%20Excel%20chart.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20when%20I%20use%20the%20TREND%20function%20to%20extrapolate%20the%20dataset%20as%20a%202nd%20order%20polynomial%2C%20the%20result%20is%20limited%20to%20the%20value%20which%20is%20produced%20when%20solving%20the%20quadratic%20equation%20with%20the%20%2BSQRT(B%5E2-4AC)%20formulation.%26nbsp%3B%20Is%20there%20anyway%20for%20me%20to%20control%20the%20TREND%20function%20to%20avoid%20this%20ambiguity%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-375666%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETrend%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Occasional Visitor

Hello,

 

I have a small 4-pair (x,y) data set that I'm trying to extrapolate using a 2nd order curve-fit.  I have the  desired value of "y" and I want the corresponding "x", which may be a negative number.  If I plot the data in an Excel chart, and "forecast" the 2nd order trendline, I get a reasonable answer.  If I use the LINEST function, calculate the polynomial coefficients and solve the quadratic equation, I get a reasonable answer if I control the (+/-) option on the quantity +/-SQRT(B^2-4AC).  There's two real solutions (roots) to the quadratic.  Using the -SQRT(B^2-4AC) option, yields a result which is compatible with the trendline forecast on the Excel chart.

 

However, when I use the TREND function to extrapolate the dataset as a 2nd order polynomial, the result is limited to the value which is produced when solving the quadratic equation with the +SQRT(B^2-4AC) formulation.  Is there anyway for me to control the TREND function to avoid this ambiguity?

0 Replies