polynomial LINEST stress!

[ben.fulcher]

Hi, I've been trying for hours to do what I've been doing on a windows
version of excel for a while now. I just can't work out why it isn't
working on my mac Excel 2004 (11.2.3). I'm trying to do polynomial fits
using LINEST, in which I usually type something like

=LINEST(y-range,x-range^{1,2},TRUE,TRUE)

(for a quadratic fit, for example) and then evaluate. But for some
reason this spits out wrong values when I evaluate the expression. I've
tried it on a few macs with Excel 2004 all with the same problematic
result. For example, you can try setting a column of x-values with
1,2,3,4 and then y-values 1,4,9,16. When you try a quadratic fit on
this, you would expect just x(squared)1,0,0), but you don't, you get
(0,4.5,-1.5) which is ridiculous! The equation you get from imposing a
treadline on a x-y scatter graph is correct, but I find it clumsy to
copy the coefficients from there when I'm used to just using LINEST.

Since it works fine for linear, data, I'm sure the problem is in the
notation ^{1,2}. Is the syntax different on mac? I can't work it out, I
appreciate any feedback,

Thanks alot

Ben

 

[JE McGimpsey]

Hi, I've been trying for hours to do what I've been doing on a windows
version of excel for a while now. I just can't work out why it isn't
working on my mac Excel 2004 (11.2.3). I'm trying to do polynomial fits
using LINEST, in which I usually type something like

=LINEST(y-range,x-range^{1,2},TRUE,TRUE)

(for a quadratic fit, for example) and then evaluate. But for some
reason this spits out wrong values when I evaluate the expression. I've
tried it on a few macs with Excel 2004 all with the same problematic
result. For example, you can try setting a column of x-values with
1,2,3,4 and then y-values 1,4,9,16. When you try a quadratic fit on
this, you would expect just x(squared)1,0,0), but you don't, you get
(0,4.5,-1.5) which is ridiculous! The equation you get from imposing a
treadline on a x-y scatter graph is correct, but I find it clumsy to
copy the coefficients from there when I'm used to just using LINEST.

Since it works fine for linear, data, I'm sure the problem is in the
notation ^{1,2}. Is the syntax different on mac? I can't work it out, I
appreciate any feedback,

This is a bug in the way XL04 (and XLv.X, not sure about earlier
versions). It's a problem with how XL handles the array generated by
^{1,2}. I've reported it again to MacBU and will post a reply if I get
one.

One workaround:

=LINEST(TRANSPOSE(y_range),TRANSPOSE(x_range)^{1;2},TRUE,TRUE)


Another, rather inflexible workaround would be to array-enter this into
an unused portion of the worksheet (xrange rows by 2 columns):

=xrange^{1,2}

then use that range as the known x's in LINEST.

 

[celldude]

Hi,
Yes that transpose thing (with the semicolons) works great. It is
rather annoying though , and strange that no one has responded to it
since excel's release? Anyway, thanks so much for your reply, that
helps alot.

Ben

 

[celldude]

Hi,
Yes that transpose thing (with the semicolons) works great. It is
rather annoying though , and strange that no one has responded to it
since excel's release? Anyway, thanks so much for your reply, that
helps alot.

Ben

 

[celldude]

Hi,
Yes that transpose thing (with the semicolons) works great. It is
rather annoying though , and strange that no one has responded to it
since excel's release? Anyway, thanks so much for your reply, that
helps alot.

Ben