Polynomial Regression Equation Formulas?

[conwaycom]

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

 

[RalphA]

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]

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]

This may help.
Regards,
Rory

 

[RalphA]

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.