SOLVED

Polynomial regression using Analysis ToolPack

%3CLINGO-SUB%20id%3D%22lingo-sub-1753587%22%20slang%3D%22en-US%22%3EPolynomial%20regression%20using%20Analysis%20ToolPack%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1753587%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20perform%20a%20%3CSTRONG%3Enonlinear%20polynomial%20regression%3C%2FSTRONG%3E%20(for%20example%20y%20%3D%20ax%C2%B2%20%2B%20bx%20%2B%20c)%20and%20obtain%2C%20in%20addition%20with%20the%20equation%20and%20R%C2%B2%2C%20the%20conficende%20interval%20and%20p-value%20of%20the%20different%20coefficients.%20Such%20information%20are%20provided%20(in%20%3CSTRONG%3EExcel%202019%3C%2FSTRONG%3E)%20for%20linear%20univariate%20regression%20by%20the%20%3CSTRONG%3EData%20Analysis%20ToolPack%3C%2FSTRONG%3E%20but%20other%20types%20of%20regression%20%3CU%3Eare%20not%20supported%3C%2FU%3E%20by%20the%20ToolPack.%3CBR%20%2F%3E%3CBR%20%2F%3EThis%20is%20strange%20because%20I%20found%20on%20forums%20and%20tutorials%20(see%20links%20below)%20that%20%3CSTRONG%3Eprevious%20versions%20of%20Excel%20did%20include%20nonlinera%20polynomial%20regression%20in%20the%20analysis%20ToolPack%3C%2FSTRONG%3E.%20How%20can%20I%20recover%20such%20tool%20%3F%3CBR%20%2F%3E%3CBR%20%2F%3Esecond%20answer%20on%20this%20forum%20%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fstackoverflow.com%2Fquestions%2F10857252%2Fquadratic-and-cubic-regression-in-excel%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fstackoverflow.com%2Fquestions%2F10857252%2Fquadratic-and-cubic-regression-in-excel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.real-statistics.com%2Fmultiple-regression%2Fpolynomial-regression%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.real-statistics.com%2Fmultiple-regression%2Fpolynomial-regression%2F%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ffacultystaff.richmond.edu%2F~rdominey%2F301%2FExcel4.html%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Ffacultystaff.richmond.edu%2F~rdominey%2F301%2FExcel4.html%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20I%20have%20to%20install%20older%20versions%20of%20Excel%20to%20get%20back%20this%20feature%20or%20is%20there%20an%20other%20way%20to%20go%20%3F%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%20!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1753587%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1754311%22%20slang%3D%22en-US%22%3ERe%3A%20Polynomial%20regression%20using%20Analysis%20ToolPack%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1754311%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F823310%22%20target%3D%22_blank%22%3E%40EdouardS%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThere%20is%20no%20special%20option%20for%20polynomial%20regression%2C%20never%20has%20been.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20the%20links%20in%20your%20post%20explain%2C%20you%20have%20to%20create%20an%20extra%20column%3A%20to%20the%20right%20of%20the%20column%20with%20the%20x-values%2C%20use%20formulas%20to%20return%20the%20square%20of%20the%20x-values%20(i.e.%20x%C2%B2).%20These%20formulas%20are%20of%20the%20form%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DA2%5E2%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEnter%20the%20range%20consisting%20of%20the%20column%20with%20the%20x-values%20AND%20the%20column%20with%20their%20squares%20in%20the%20Input%20X%20Range%20box.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S3562.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F224838i3AEF65B6D4DA1214%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22S3562.png%22%20alt%3D%22S3562.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi everyone,

 

I would like to perform a nonlinear polynomial regression (for example y = ax² + bx + c) and obtain, in addition with the equation and R², the conficende interval and p-value of the different coefficients. Such information are provided (in Excel 2019) for linear univariate regression by the Data Analysis ToolPack but other types of regression are not supported by the ToolPack.

This is strange because I found on forums and tutorials (see links below) that previous versions of Excel did include nonlinera polynomial regression in the analysis ToolPack. How can I recover such tool ?

second answer on this forum : https://stackoverflow.com/questions/10857252/quadratic-and-cubic-regression-in-excel 

https://www.real-statistics.com/multiple-regression/polynomial-regression/ 

https://facultystaff.richmond.edu/~rdominey/301/Excel4.html 

 

Do I have to install older versions of Excel to get back this feature or is there an other way to go ?

Thank you !

2 Replies
Highlighted
Best Response confirmed by EdouardS (New Contributor)
Solution

@EdouardS 

There is no special option for polynomial regression, never has been.

 

As the links in your post explain, you have to create an extra column: to the right of the column with the x-values, use formulas to return the square of the x-values (i.e. x²). These formulas are of the form

 

=A2^2

 

Enter the range consisting of the column with the x-values AND the column with their squares in the Input X Range box.

 

S3562.png

Highlighted
Ow right, I thing I accidentally swap the Xs and Y when I tried last time. Thank you very much for your help !