Another LINEST bug
 

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

Another LINEST bug
 

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Jerry W. Lewis" wrote in message
...
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

Another LINEST bug
 

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.

--
Biff
Microsoft Excel MVP


"Bernard Liengme" wrote in message
...
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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Jerry W. Lewis" wrote in message
...
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

Another LINEST bug
 

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.
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Bernard Liengme" wrote in message
...
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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Jerry W. Lewis" wrote in message
...
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

Another LINEST bug
 

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

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Jerry W. Lewis" wrote in message
...
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