Trendline and equation doesnot match

[sxu]

Hi,

I am working on chart trendline with the following data.
y x
85 30
105 38.8
110 40.1
135 56.1
155 74.4
180 103.6
185 108.5
190 130.7
195 132.4
The equation is a polynomial with an order of 4.
I use 10 decimal for precision, and the equation comes out as:
y = 0.0305944056x4 - 0.9780497281x3 + 8.5941142191x2 - 8.7305749811x + 88.3333333344
Rē = 0.9897505143

Plug in x=45 into the equation, I got a Y for more than 53000, which from the trendline it should be around 130. I started with 4 decimal places, the Y was more than 54000; increase to 10 decimal places, Y is now more than 53000. Still way off the 130.

Anyone has any good solution?

Thanks for any help.

[shg]

It's customary to put the independent variable on the left:

Row\Col	A	B	C	D	E	F	G	H	I
1	x	y
2	30.0	85		-1.965E-06	6.904E-04	-9.402E-02	6.736E+00	-4.953E+01	D2:H2: {=LINEST(B2:B10, A2:A10^{1,2,3,4})}
3	38.8	105
4	40.1	110					x	y
5	56.1	135					45	118.0	H5: =SERIESSUM(G5, 4, -1, $D$2:$G$2) + $H$2
6	74.4	155
7	103.6	180
8	108.5	185
9	130.7	190
10	132.4	195

The LINEST formula is an array formula; you need to select all of the cells (D2:H2 here), paste the formula in the formula bar, press and hold the Ctrl and Shift keys, then press Enter. That will make the curly braces appear. You can't enter them manually.

[sxu]

Thanks, Shg.

I paste the formula "=LINEST(B2:B10, A2:A10^{1,2,3,4})" into each of cells D2:H2, and press ctrl+shift+enter. However, I got all D2:H2 as -1.965E-06.
Which part am I doing it wrong?

[shg]

Not into EACH, into ALL

select all of the cells (D2:H2 here), paste the formula in the formula bar, press and hold the Ctrl and Shift keys, then press Enter.

[sxu]

Thanks, shg. I got it corrected.
Thank you a lot for your help.

[shg]

You're welcome.

[sxu]

shg, sorry to trouble you again.
I am still working on the same set of data, but i added two more actual data points.
x y
0 0
188.3 220
Now I figured that a log function will be the best fit. How is the array function for log works?
My current log array formula is D4:N4: {=LOGEST(B2:B12,A2:A12,TRUE,FALSE)}.

Thank you again for your help.

[MrShorty]

I'm not certain exactly what your question is. Does this information help: https://support.office.com/en-us/art...b-a272c1d18b4b

Note that LOGEST() fits to the equation y=b*m^x rather than the more common b*e^(mx) that many expect.