Another LINEST bug

[Jerry W. Lewis]

Help for LINEST states that

The array known_x's can include one or more sets of variables. If only one
variable is used, known_y's and known_x's can be ranges of any shape, as long
as they have equal dimensions. If more than one variable is used, known_y's
must be a vector (that is, a range with a height of one row or a width of one
column).

In particular,
=LINEST({1,1.5;2.5,3},{1,2;2,3},,TRUE)
should be equivalent to either
=LINEST({1;1.5;2.5;3},{1;2;2;3},,TRUE)
or
=LINEST({1,1.5,2.5,3},{1,2,2,3},,TRUE)

Prior to 2003, they were equivalent (as documented), but in Excel 2003, not
even the degrees of freedom for =LINEST({1,1.5;2.5,3},{1,2;2,3},,TRUE) match
the documented behavior.

What happens in 2007?

Jerry

 

[Bernard Liengme]

Jerry,
In XL2007 with all three I get
1 2.22045E-16
0.353553 0.75
0.8 0.5
8 2

And trendline on the last dataset gives the same slope and r², but intercept
zero

Please remind me: If I have a formula such as one of yours, how do I copy
and paste and make the array formula. Right now I must select all the cells
and then type it. I know there is a way but I have forgotten.
best wishes

 

[T. Valko]

how do I copy and paste and make the array formula.

If I understand, select the range of cells and paste the formula into the
formula bar then array enter.

 

[Bernard Liengme]

Many thanks, Biff!
It odd but I generally 'compose' in the Formula Bar but paste into cells,
so I missed the trick.
Have a great weekend.

 

[Jerry W. Lewis]

Thanks for the info. Apparently MS's post SP2 patch to LINEST did not fix
all of the issues that they knew it had.

Jerry