LINEST - Polynomial equation

[pandan]

Hello, I have a problem with the LINEST function and even when I try given examples it doesn't work.

X..............Y
100........-0,5
400........-0,7
700........-0,3
1000........0,1
1100........0,2
1200........0,2

There are my X and Y's. What I want is to get the constants for an third degree equation in four cells. I can do a graph and then a trendline to get the equation: y = -3,07848796E-09x3 + 6,81164335E-06x2 - 3,38519903E-03x - 2,28863584E-01

When I change the values in X and Y the equation changes and since there ain't possible to link the equation in the chart to a cell I cant use the trendline.

I've looked around and found out about the LINEST function. The strange part is that it won't work for me.

The formulas I typed:
=INDEX(LINEST(B2:B7;A2:A7^{1;2;3});1)
=INDEX(LINEST(B2:B7;A2:A7^{1;2;3});1;2)
=INDEX(LINEST(B2:B7;A2:A7^{1;2;3});1;3)
=INDEX(LINEST(B2:B7;A2:A7^{1;2;3});1;4)

The only result I get is #VALUE!.

If someone could help me I would be really grateful. You can either post here or send me an PM or an e-mail with, for example an Excel file were you show me how to do.

English or Swedish is ok in PM or mail.

Regards!
Niklas

 

[Aladin Akyurek]

Book4
	A	B	C	D	E	F	G	H	I	J
1	X	Y
2	100	-0.5		-3.07849E-09	6.81164E-06	-0.00339	-0.22886			-3.1E-09
3	400	-0.7		2.55109E-10	4.97447E-07	0.000272	0.037367			6.81E-06
4	700	-0.3		0.998918483	0.020183445	#N/A	#N/A			-0.00339
5	1000	0.1		615.7513879	2	#N/A	#N/A			-0.22886
6	1100	0.2		0.75251859	0.000814743	#N/A	#N/A
7	1200	0.2
8
Sheet1

D2:G6 is obtained by entering

=LINEST(B2:B7,A2:A7^{1,2,3},TRUE,TRUE)

in D2 then selecting D2:G6 and confirming the formula with control+shift+enter.

With INDEX()...

J2:

=INDEX(LINEST($B$2:$B$7,$A$2:$A$7^{1,2,3},TRUE,TRUE),1,ROW()-ROW($J$2)+1)

which you must confirm with control+shift+enter then copy down.

Note that the list separator on my American system is comma, not semi-colon.

 

[pandan]

I really don't know what the problem is but when I do as you just did I still just get #VALUE! in D2:G6

Do I need some special add-in or what is the problem? Maybe you could send me an excel file by mail?


EDIT: I now looked at "Evaluate formula" and I saw this:

LINEST($B$2:$B$7;{100;160000;343000000;#N/A;#N/A;#N/A};TRUE;TRUE)

100 is A2^1
160000 is A3^2
343000000 is A4^3
then it become #N/A and maybe it's because I don't have any ^4, ^5 and ^6...

Have I some settings that are wrong or why wont it work?

 

[Aladin Akyurek]

I really don't know what the problem is but when I do as you just did I still just get #VALUE! in D2:G6

Do I need some special add-in or what is the problem? Maybe you could send me an excel file by mail?

You don't need a special add-in.
For an Excel file, I'll need your e-mail via PM.

Did you apply CONTROL+SHIFT+ENTER properly?

 

[pandan]

I really don't know what the problem is but when I do as you just did I still just get #VALUE! in D2:G6

Do I need some special add-in or what is the problem? Maybe you could send me an excel file by mail?

You don't need a special add-in.
For an Excel file, I'll need your e-mail via PM.

Did you apply CONTROL+SHIFT+ENTER properly?

Yes I did.

Check my last post, now edited.

 

[Aladin Akyurek]

I really don't know what the problem is but when I do as you just did I still just get #VALUE! in D2:G6

Do I need some special add-in or what is the problem? Maybe you could send me an excel file by mail?

You don't need a special add-in.
For an Excel file, I'll need your e-mail via PM.

Did you apply CONTROL+SHIFT+ENTER properly?

Yes I did.

Check my last post, now edited.

Would you try...

{=LINEST(B2:B7;A2:A7^{1\2\3};TRUE;TRUE)}

for \ might be the separator in a constant array on your system?

 

[pandan]

It now works properly. A big thanks to Aladin Akyurek for all the help.