Polynomial Regression Equation Formulas?

conwaycom

New Member
Joined
Mar 18, 2008
Messages
2
I know you can view various trend lines in Excel by adding a trend line to an existing chart. Furthermore, you can view the formula on the chart which provides the equation that describes the tend line shown.

Background:
In my model, I'm trying to identify numerous (50+) unique polynomial equations (6th order, based on X/Y scatterplot relationships). Currently, the only way to identify the formula is to build 50+ charts, show the trend line, and then manually enter the regression equation shown.

Question:
Is there a way for excel to automatically generate the regression equation (6th order, polynomial)? This would prevent the entire manual entry process.

Thanks,
Steve
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
Perhaps this will work for you!

Enter your data in, say, A1:B30
Click on Tools, Macro, Record New Macro
Highlight the area A1:B30
Click on the Chart Wizard, choose, say, Ctrl+Shift+c as hot-keys, and construct your xy(scatter) chart, using the curve type of your choice (polynomial of the 6th degree, for you).
Right-click on the curve, and choose Trend Line, then, Options, and check the Show Trend-line Equation.
Close the macro

Enter the new data in the range A1:B30, and see the new results, including the new trend line. Or, delete the chart, enter the new data, then press Ctrlss+Shift+C...
 

conwaycom

New Member
Joined
Mar 18, 2008
Messages
2
Good idea regarding the macro.

Using your example, I'm wondering if excel can use the data in A1:B30 (x and y variables), and then product the regression equation based on those inputs (6th degree, polynomial). Or, perhaps there's a way of enabling excel to provide the X1 variable, x2 variable, etc, in individual cells, which enables me to piece them together to form the x1 + x2 + x3, etc. in a master formula?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,010
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
This may help.
Regards,
Rory
 

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
Ah, my suggestion will give the equation for y, based on x. It seems you want an equation for x, based on y, right? Would swapping the x and y values give you the equation you need? Not positive about this, but, it might be worth a try.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,519
Messages
5,469,111
Members
406,637
Latest member
Mbsmbs

This Week's Hot Topics

Top