Linest #Value error

[johansoder]

Hi,

I am trying to get the constants for a third order polynomial. I have tried the LINEST (and with INDEX) function but I get a #VALUE error. I have tried ctrl+shift+enter and without it and it still gives error. I will post an example textbook but also my formula here:

Please Login or Register  to view this content.

I don't know if there is problem when using semicolon instead of comma . All examples I read did use (,), but since I am using european version of excel I have the semi colon instead.

All help is appreciated!

[MrShorty]

It looks to me like the problem is with the semi-colon versus comma in the array constant {1;2;3}. When I open your file, Excel has automatically replaced the semi-colons separating the function arguments with commas (consistent with my system settings). However, the semi-colons inside the array constant are not changed. If I change these to commas, then the function works just fine.

I don't use array constants a lot, but I suspect that the choice of separator in array constants is not controlled by regional settings. It may be consistently comma separates "fields across a row" where semi-colons separate "rows" (thinking of array constants as 2 dimensional arrays).

[johansoder]

Thanks for the answer.

However, when I try with commas instead, in the array bracket, excel gives me an error and tries to change the array to {1,23} instead of {1,2,3}. I think I don't understand the formula well enough to know what's happening there. I will try to change my system settings to see if it works your way.

[johansoder]

I solved it, for anyone with future problems;

Instead of

Please Login or Register  to view this content.

With europe region excel you have to use backslash instead of ; in the array forumla:

Please Login or Register  to view this content.