SOLVED

Computing polynomial coefficients for a data set

%3CLINGO-SUB%20id%3D%22lingo-sub-154831%22%20slang%3D%22en-US%22%3EComputing%20polynomial%20coefficients%20for%20a%20data%20set%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-154831%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20column%20of%20data.%20I%20can%20chart%20the%20data%20and%20create%20a%20polynomial%20trendline.%20I%20can%20show%20the%20trendline%20equation%20and%20see%20the%20coefficients.%20But%20is%20there%20a%20way%20to%20compute%20the%20polynomial%20coefficients%20for%20a%20range%20of%20data%3F%20I%20need%20to%20use%20these%20values%20in%20a%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-154831%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-154885%22%20slang%3D%22en-US%22%3ERe%3A%20Computing%20polynomial%20coefficients%20for%20a%20data%20set%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-154885%22%20slang%3D%22en-US%22%3EPerfect%2C%20thanks!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-154840%22%20slang%3D%22en-US%22%3ERe%3A%20Computing%20polynomial%20coefficients%20for%20a%20data%20set%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-154840%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20may%20calculate%20the%20directly%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22http%3A%2F%2Fspreadsheetpage.com%2Findex.php%2Ftip%2Fchart_trendline_formulas%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fspreadsheetpage.com%2Findex.php%2Ftip%2Fchart_trendline_formulas%2F%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1872037%22%20slang%3D%22en-US%22%3ERe%3A%20Computing%20polynomial%20coefficients%20for%20a%20data%20set%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1872037%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3EHello%2C%20your%20link%26nbsp%3B%26nbsp%3B%3CA%20href%3D%22http%3A%2F%2Fspreadsheetpage.com%2Findex.php%2Ftip%2Fchart_trendline_formulas%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fspreadsheetpage.com%2Findex.php%2Ftip%2Fchart_trendline_formulas%2F%3C%2FA%3E%3C%2FP%3E%3CP%3EHas%20this%20link%20changed%3F%26nbsp%3B%20Your%20line%20redirects%20me%20to%20the%20spreadsheetpage.%26nbsp%3B%20Am%20i%20missing%20the%20right%20idea%20here%3F%26nbsp%3B%20Thank%20you%20for%20any%20direction%20you%20can%20give.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EI%20am%20using%20the%20polynomial%20given%20to%20me%20in%20the%20trendline%2C%206th%20order%20but%20it%20does%20not%20compute%20properly.%26nbsp%3B%20I%20am%20fairly%20deft%20with%20bracketing%20and%20such%2C%20reviewed%20my%20formula%20many%20times%20and%20the%20values%20are%20outlandish.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3ERik%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1874082%22%20slang%3D%22en-US%22%3ERe%3A%20Computing%20polynomial%20coefficients%20for%20a%20data%20set%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1874082%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F864074%22%20target%3D%22_blank%22%3E%40Camerik57%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELooks%20like%20it's%20gone.%20Sorry%2C%20I%20don't%20know%20there%20to%20find%20this%20page.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1874292%22%20slang%3D%22en-US%22%3ERe%3A%20Computing%20polynomial%20coefficients%20for%20a%20data%20set%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1874292%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F864074%22%20target%3D%22_blank%22%3E%40Camerik57%3C%2FA%3E%26nbsp%3B%26nbsp%3B%20Re%3A%20%60%60I%20am%20using%20the%20polynomial%20given%20to%20me%20in%20the%20trendline%2C%206th%20order%20but%20it%20does%20not%20compute%20properly%60%60%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20copying%20the%20coefficient%20from%20the%20trendline%20label%2C%20be%20sure%20to%20format%20the%20TL%20to%20display%2015%20significant%20digits.%26nbsp%3B%20I%20prefer%20Scientific%20with%2014%20decimal%20places.%20That%20works%20regardless%20of%20the%20magnitude%20of%20the%20coefficients.%26nbsp%3B%20But%20if%20all%20of%20the%20coefficients%20are%20%22close%22%20in%20magnitude%2C%20you%20might%20use%20Number%20with%20sufficient%20decimal%20places.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEven%20better%3A%26nbsp%3B%20Use%20LINEST%20to%20generate%20the%20coefficients.%26nbsp%3B%20For%20example%2C%20select%207%20consecutive%20horizontal%20cells%20(e.g.%20A1%3AG1)%2C%20and%20array-enter%20(press%20ctrl%2Bshift%2BEnter%20instead%20of%20just%20Enter)%20a%20formula%20of%20the%20form%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DLINEST(Y1%3AY100%2C%20Z1%3AZ100%5E%7B1%2C2%2C3%2C4%2C5%2C6%7D)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20also%20note%20that%20most%20polynomial%20trendlines%20are%20useful%20only%20for%20interpolating%20estimated%20Y%20values.%26nbsp%3B%20If%20you%20try%20to%20extrapolate%20(i.e.%20for%20X%20values%20outside%20the%20original%20range)%2C%20often%20the%20trendline%20formula%20%22blows%20up%22%2C%20taking%20wildly%20unexpected%20turns%20(literally).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20need%20more%20assistance%2C%20please%20attach%20an%20example%20Excel%20file%20with%20representative%20data%20that%20demonstrates%20the%20problem%20that%20you%20perceive.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have a column of data. I can chart the data and create a polynomial trendline. I can show the trendline equation and see the coefficients. But is there a way to compute the polynomial coefficients for a range of data? I need to use these values in a formula.

5 Replies
Best Response confirmed by skiprichards (New Contributor)
Perfect, thanks!

@Sergei BaklanHello, your link  http://spreadsheetpage.com/index.php/tip/chart_trendline_formulas/

Has this link changed?  Your line redirects me to the spreadsheetpage.  Am i missing the right idea here?  Thank you for any direction you can give. 

I am using the polynomial given to me in the trendline, 6th order but it does not compute properly.  I am fairly deft with bracketing and such, reviewed my formula many times and the values are outlandish. 

Rik 

@Camerik57 

Looks like it's gone. Sorry, I don't know there to find this page.

@Camerik57   Re: ``I am using the polynomial given to me in the trendline, 6th order but it does not compute properly``

 

If you copying the coefficient from the trendline label, be sure to format the TL to display 15 significant digits.  I prefer Scientific with 14 decimal places. That works regardless of the magnitude of the coefficients.  But if all of the coefficients are "close" in magnitude, you might use Number with sufficient decimal places.

 

Even better:  Use LINEST to generate the coefficients.  For example, select 7 consecutive horizontal cells (e.g. A1:G1), and array-enter (press ctrl+shift+Enter instead of just Enter) a formula of the form:

 

=LINEST(Y1:Y100, Z1:Z100^{1,2,3,4,5,6})

 

However, also note that most polynomial trendlines are useful only for interpolating estimated Y values.  If you try to extrapolate (i.e. for X values outside the original range), often the trendline formula "blows up", taking wildly unexpected turns (literally).

 

If you need more assistance, please attach an example Excel file with representative data that demonstrates the problem that you perceive.