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
[....]
| F | G | H |
|---|
| 28 |
| Known X -> | 50 |
| 29 |
|
|
|
| 30 | x2 | -0.381 | 362.9025 |
| 31 | x | -3.6076 | -180.38 |
| 32 | Constant | 39.792 | 39.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>
You made two mistakes.
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)
