Problem with cubic polynomial curve fit calculation ?

[Joe Miller]

The attached excel program is comprised of 3 spreadsheets ... a linear curve fit, a quadratic fit and a cubic fit.

The calculated ''y on day x'' value is correct for the linear and the quadratic but is wrong for the cubic [i.e.] it is presented as 1.1089
but is displayed on the chart as approximately 1.113?

Please let me know if you see what i am doing wrong.

[shg]

You're only using one significant digit for the x^3 term. Use LINEST instead:

Row\Col	A	B	C	D	E
2	date	daynum	close
3	28-Aug	1	1.11800
4	31-Aug	2	1.12095
5	01-Sep	3	1.13130
6	02-Sep	4	1.12255
7	03-Sep	5	1.11230
8	04-Sep	6	1.11440
9		a3=	0.000718		C9:C12: {=TRANSPOSE(LINEST(C3:C8, B3:B8^{1,2,3}))}
10		a2=	-0.009087
11		a1=	0.032088
12		a0=	1.09265
13		day num x=	6
14		y on day x=	1.1131		C14: =SERIESSUM(C13, 3, -1, $C$9:$C$11) + $C$12

[Joe Miller]

Thanks shg,
Great reply & much appreciated.
Why is a cubic fit such a big deal, especially given the power & sophistication of Excel?

[shg]

You're welcome.

It's not a big deal at all ...

[Joe Miller]

i have never experienced the treatment of the form ''x raised to the integer n''
to be so different for n=2 and n=3.

[shg]

Are you referring to the SeriesSum function?

You could do it as you were previously; it's just more compact and flexible with SeriesSum.

[Joe Miller]

y = ao + a1*x
y = ao + a1*x + a2*x^2
y = ao + a1*x + a2*x^2 + a3*x^3

i simply wonder why as i progress from quadratic to cubic i
suddenly must use a different method to correctly compute y?
[i.e.] why cannot i just simply use the equation y = ao + a1*x + a2*x^2 + a3*x^3?

[shg]

why cannot i just simply use the equation y = ao + a1*x + a2*x^2 + a3*x^3?

You can. I don't understand what's confusing you.

[Joe Miller]

per the original post & attachment, I was getting an incorrect y value for the
cubic solution. your fix [which I blindly applied, having no idea what it does]
fixed the problem.

obviously I am unknowingly doing something goofy. i will go back to square one using
y = a0 + a1*x + a2*x^2 + a3*x^3 and see what happens.

if you have time, please take another look at the attachment in my original post.

thanks a lot for your help.

[shg]

The problem isn't how you calculate y from the coefficients, it is that you are copying the coefficients as they appear in the trendline equation with the very limited precision with which they are shown.

All of your fits could be solved in the same way:

Row\Col	A	B	C	D	E	F	G	H	I	J	K	L	M
1						x^5	x^4	x^3	x^2	x^1	b
2	date	daynum	close		linear					-0.00151	1.12519		G2:H2: {=LINEST($C$3:$C$8, $B$3:$B$8)}
3	28-Aug	1	1.118		quadratic				-0.00155	0.00933	1.11075		F3:H3: {=LINEST($C$3:$C$8, $B$3:$B$8^{1,2})}
4	31-Aug	2	1.12095		cubic			0.00072	-0.00909	0.03209	1.09265		E4:H4: {=LINEST($C$3:$C$8, $B$3:$B$8^{1,2,3})}
5	01-Sep	3	1.1313		quartic		0.0008	-0.01105	0.04699	-0.07215	1.15318		G5:K5: {=LINEST($C$3:$C$8, $B$3:$B$8^{1,2,3,4})}
6	02-Sep	4	1.12255		quintic	-0.0004	0.0078	-0.05669	0.18423	-0.25787	1.2409		F6:K6: {=LINEST($C$3:$C$8, $B$3:$B$8^{1,2,3,4,5})}
7	03-Sep	5	1.1123
8	04-Sep	6	1.1144

[Joe Miller]

Thanks for the in depth tutorial presentation … much appreciated.
I have a lot to play with and absorb now but I think I am on the
way to understanding what you and LINEST are doing.

My understanding is that a precision of 4 decimal places is insufficient
coefficient precision … and that LINEST resolves that problem.

1] Is my understanding correct?
2] Is there a way to make the trend line equation display more coefficient precision?

[shg]

My understanding is that a precision of 4 decimal places is insufficient

The number of significant digits is a more significant measure than the number of decimal places.

and that LINEST resolves that problem.

Yes, in the sense that it returns the full precision of the result.

Is there a way to make the trend line equation display more coefficient precision?

Yes, you can format the trendline label to show the coefficients in scientific notation. Be aware LINEST and chart trendlines are calculated differently, so there may be some differences.

[Joe Miller]

many thanks. i will try to implement all this info to see if i can make it work.

[shg]

You're welcome.