# Polynomial regression equation?

#### DEllis81

##### Board Regular
I have created a trend line regression resulting (within x/y chart) resulting with equation -.381x^2 - 3.6076x^1 + 39.792. Based on the data, an X value of 50, should return a Y predicted value near 30.5. However, you can see my result is near 222... My exponential math is rusty enough, that I'm not confident in exact format - but found similar formula elsewhere..

daryl

Excel 2010
FGH
28Known X ->50
29
30x2-0.381362.9025
31x-3.6076-180.38
32Constant39.79239.792
33Predicted Y ->222.3145
Cell Formulas
RangeFormula
H30=(G30*\$H\$28)^2
H31=(G31*\$H\$28)^1
H32=G32
H33=SUM(H30:H32)

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Hi,
just curious how did you get the poly formula? was is with linest? if yes what what your data in X and y?

I have created a trend line regression resulting (within x/y chart) resulting with equation -.381x^2 - 3.6076x^1 + 39.792. Based on the data, an X value of 50, should return a Y predicted value near 30.5. However, you can see my result is near 222
[....]

FGH
28
Known X ->50
29

30x2-0.381362.9025
31x-3.6076-180.38
32Constant39.79239.792
33
Predicted Y ->222.3145

<tbody>
</tbody>
[....]
Worksheet Formulas
Cell
Formula
H30=(G30*\$H\$28)^2
H31=(G31*\$H\$28)^1
H32=G32
H33=SUM(H30:H32)

<tbody>
</tbody>

<tbody>
</tbody>

First, the constants that appear in the trendline formula in a chart are rounded to very low precision. Usually, the precision is not sufficient to reproduce the predicted Y.

One remedy is to display all trendline constants with 15 significant digits, the most that Excel will format. I prefer to use Scientific format with 14 decimal places, because that displays 15 significant digits regardless of the magnitude.

Right-click on the trendline formula, then click on Format Trendline Label, Number, Scientific, then enter 14 into the Decimal Places field, and click on Close. The constant will look like 1.2345678901234E+12; it means 1.2345678901234*(10^12). Copy and paste the constants into cells in the Excel worksheet.

Alternatively, usually it is better to use LINEST to create the constants. But you do not provide sufficient context for us to provide exactly the LINEST formula to use. If you are interested, upload an example workbook to a file-sharing website, then post the shared/public URL of the file in a response.

Second, you have misinterpreted the trendline formula. For example, instead of =(G30*\$H\$28)^2, you should write either =G30*\$H\$28^2 or =G30*(\$H\$28^2), which are equivalent.

Alternatively, you could write =SUMPRODUCT(\$G\$30:\$G\$32*H28^{2,1,0}) or =SERIESSUM(H28,2,-1,\$G\$30:\$G\$32)

Last edited:

Replies
0
Views
802
Replies
5
Views
520
Replies
8
Views
2K
Replies
3
Views
829
Replies
0
Views
1K

1,196,183
Messages
6,013,914
Members
441,795
Latest member
Lilium

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back