Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Peter N
 
Posts: n/a
Default LINEST maximum number of predictor variables

I am using Excel 2002. It seems to be unable to handle any more than 16
predictor variables. All it returns is a #REF error. The help does not
document any limit. Is this an undocumented limitation? Is Excel 2003
LINEST capable of handling more that 16 variables? How many?
  #2   Report Post  
Harlan Grove
 
Posts: n/a
Default

Peter N wrote...
I am using Excel 2002. It seems to be unable to handle any more than

16
predictor variables. All it returns is a #REF error. The help does

not
document any limit. Is this an undocumented limitation? Is Excel

2003
LINEST capable of handling more that 16 variables? How many?


What's your *EXACT* formula? LINEST should return #VALUE! and #NUM!
errors when it can't invert the bilinear form of the independent
variables, but it only returns #REF! when there's a true range
reference error.

  #3   Report Post  
Jerry W. Lewis
 
Posts: n/a
Default

LINEST also returns #REF! when you exceed its hardcoded limit on
predictors. Empirically, that limit appears to be 17 predictors if no
constant is fitted, or 16 predictors and a fitted constant.

As a practical matter I would be very suspicious of the numerical
properties of LINEST solutions long before hitting that hard coded
limit. Perhaps that is why it is there, even though MINVERSE in theory
can invert a 52x52 X'X matrix.

Jerry

Harlan Grove wrote:

Peter N wrote...

I am using Excel 2002. It seems to be unable to handle any more than

16

predictor variables. All it returns is a #REF error. The help does

not

document any limit. Is this an undocumented limitation? Is Excel

2003

LINEST capable of handling more that 16 variables? How many?


What's your *EXACT* formula? LINEST should return #VALUE! and #NUM!
errors when it can't invert the bilinear form of the independent
variables, but it only returns #REF! when there's a true range
reference error.


  #4   Report Post  
Peter N
 
Posts: n/a
Default

My reply is at the bottom.

"Jerry W. Lewis" wrote:

LINEST also returns #REF! when you exceed its hardcoded limit on
predictors. Empirically, that limit appears to be 17 predictors if no
constant is fitted, or 16 predictors and a fitted constant.

As a practical matter I would be very suspicious of the numerical
properties of LINEST solutions long before hitting that hard coded
limit. Perhaps that is why it is there, even though MINVERSE in theory
can invert a 52x52 X'X matrix.

Jerry

Harlan Grove wrote:

Peter N wrote...

I am using Excel 2002. It seems to be unable to handle any more than

16

predictor variables. All it returns is a #REF error. The help does

not

document any limit. Is this an undocumented limitation? Is Excel

2003

LINEST capable of handling more that 16 variables? How many?


What's your *EXACT* formula? LINEST should return #VALUE! and #NUM!
errors when it can't invert the bilinear form of the independent
variables, but it only returns #REF! when there's a true range
reference error.



My *EXACT* formula is =LINEST(Y14:Y50,A14:Q50,1,1)

Is this hardcoded limit documented anywhere? Does Excel 2003 handle a
larger number of channels?

I have a limited understanding of matrix math, but I understand that the
"Normal Equations" involves inverting the [X'X] matrix. To minimize roundoff
errors in the computation, each vector in the X matrix should first be
shifted (centered about its mean). Hopefully LINEST performs this operation
automatically and transparently.

I read the article http://support.microsoft.com/kb/828533#kb3 and Microsoft
acknowledges weakness in numerical methods with LINEST in Excel 2002 and
earlier. This article claims that LINEST has been improved in Excel 2003,
using QR decomposition (I'll have to read up to understand that) resulting in
more robust performance. They also advertise
€¢ Better numeric stability (generally smaller round off errors)
€¢ Analysis of collinearity issues

In what form is the result of this "Analysis of collinearity" presented to
the Excel user? What statistic in the matrix returned by LINEST should I look
at to know that the X matrix is "nearing" collinearity.

If the predictors exhibit near collinearity, the solution is to remove
predictors from the group of predictors that are nearly collinear. Is there
an easy way to identify which predictors form a group that is nearly
collinear?
  #5   Report Post  
Tushar Mehta
 
Posts: n/a
Default

In article ,=20
says...
{snip}
=20
If the predictors exhibit near collinearity, the solution is to remove=20
predictors from the group of predictors that are nearly collinear. Is t=

here=20
an easy way to identify which predictors form a group that is nearly=20
collinear?
=20

To the extent that you can trust LINEST's algorithm, it tells you a=20
predictor is redundant by setting both the coefficient and the standard=20
error to zero.

--=20
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,=20
says...
My reply is at the bottom.
=20
"Jerry W. Lewis" wrote:
=20
LINEST also returns #REF! when you exceed its hardcoded limit on=20
predictors. Empirically, that limit appears to be 17 predictors if no=

=20
constant is fitted, or 16 predictors and a fitted constant.
=20
As a practical matter I would be very suspicious of the numerical=20
properties of LINEST solutions long before hitting that hard coded=20
limit. Perhaps that is why it is there, even though MINVERSE in theory=

=20
can invert a 52x52 X'X matrix.
=20
Jerry
=20
Harlan Grove wrote:
=20
Peter N wrote...
=20
I am using Excel 2002. It seems to be unable to handle any more than

16
=20
predictor variables. All it returns is a #REF error. The help does

not
=20
document any limit. Is this an undocumented limitation? Is Excel

2003
=20
LINEST capable of handling more that 16 variables? How many?

=20
What's your *EXACT* formula? LINEST should return #VALUE! and #NUM!
errors when it can't invert the bilinear form of the independent
variables, but it only returns #REF! when there's a true range
reference error.

=20
=20

My *EXACT* formula is =3DLINEST(Y14:Y50,A14:Q50,1,1)
=20
Is this hardcoded limit documented anywhere? Does Excel 2003 handle a=20
larger number of channels?
=20
I have a limited understanding of matrix math, but I understand that the=

=20
"Normal Equations" involves inverting the [X'X] matrix. To minimize roun=

doff=20
errors in the computation, each vector in the X matrix should first be=20
shifted (centered about its mean). Hopefully LINEST performs this operat=

ion=20
automatically and transparently.
=20
I read the article
http://support.microsoft.com/kb/828533#kb3 and Microso=
ft=20
acknowledges weakness in numerical methods with LINEST in Excel 2002 and=

=20
earlier. This article claims that LINEST has been improved in Excel 2003=

, =20
using QR decomposition (I'll have to read up to understand that) resultin=

g in=20
more robust performance. They also advertise=20
=E2=A4=A2 Better numeric stability (generally smaller round off errors)=

=20
=E2=A4=A2 Analysis of collinearity issues=20
=20
In what form is the result of this "Analysis of collinearity" presented t=

o=20
the Excel user? What statistic in the matrix returned by LINEST should I =

look=20
at to know that the X matrix is "nearing" collinearity. =20
=20
If the predictors exhibit near collinearity, the solution is to remove=20
predictors from the group of predictors that are nearly collinear. Is t=

here=20
an easy way to identify which predictors form a group that is nearly=20
collinear?
=20



  #6   Report Post  
Michael R Middleton
 
Posts: n/a
Default

Peter N -

Is there an easy way to identify which predictors form a group that is
nearly collinear? <


Before looking for group collinearity, a primitive first step is to use the
Correlation tool on all predictors to look for high values of r between
pairs of prospective predictors.

- Mike

www.mikemiddleton.com


  #7   Report Post  
Jerry W. Lewis
 
Posts: n/a
Default

Peter N wrote:

....

"Jerry W. Lewis" wrote:


LINEST also returns #REF! when you exceed its hardcoded limit on
predictors. Empirically, that limit appears to be 17 predictors if no
constant is fitted, or 16 predictors and a fitted constant.

As a practical matter I would be very suspicious of the numerical
properties of LINEST solutions long before hitting that hard coded
limit. Perhaps that is why it is there, even though MINVERSE in theory
can invert a 52x52 X'X matrix.

Jerry

....
Is this hardcoded limit documented anywhere? Does Excel 2003 handle a
larger number of channels?



I have not seen it documented, unless you count
http://groups-beta.google.com/group/...daea364dd8957c

Excel 2003 has the same limit.


I have a limited understanding of matrix math, but I understand that the
"Normal Equations" involves inverting the [X'X] matrix. To minimize roundoff
errors in the computation, each vector in the X matrix should first be
shifted (centered about its mean). Hopefully LINEST performs this operation
automatically and transparently.



I do not believe that 2003 centers vectors.

I read the article http://support.microsoft.com/kb/828533#kb3 and Microsoft
acknowledges weakness in numerical methods with LINEST in Excel 2002 and
earlier. This article claims that LINEST has been improved in Excel 2003,
using QR decomposition (I'll have to read up to understand that) resulting in
more robust performance. They also advertise
€¢ Better numeric stability (generally smaller round off errors)
€¢ Analysis of collinearity issues



QR or SV (singular value) decomposition of X is definitely the most
accurate way to go, but there are issues with the 2003 implementation as
well

http://groups-beta.google.com/group/...57dccf7aa83b61

Jerry

  #8   Report Post  
Martin Brown
 
Posts: n/a
Default

Peter N wrote:

My reply is at the bottom.

"Jerry W. Lewis" wrote:

LINEST also returns #REF! when you exceed its hardcoded limit on
predictors. Empirically, that limit appears to be 17 predictors if no
constant is fitted, or 16 predictors and a fitted constant.

As a practical matter I would be very suspicious of the numerical
properties of LINEST solutions long before hitting that hard coded
limit. Perhaps that is why it is there, even though MINVERSE in theory
can invert a 52x52 X'X matrix.

Jerry

Harlan Grove wrote:


Peter N wrote...


I am using Excel 2002. It seems to be unable to handle any more than
16
predictor variables. All it returns is a #REF error. The help does


It is probably falling over and giving wrong answers at around 6 or 7
variables unless your predictors are nearly orthogonal to begin with. On
some tricky cases the old Excel LINEST could fail when fitting anything
beyond a cubic polynomial.

not
document any limit. Is this an undocumented limitation? Is Excel
2003
LINEST capable of handling more that 16 variables? How many?

What's your *EXACT* formula? LINEST should return #VALUE! and #NUM!
errors when it can't invert the bilinear form of the independent
variables, but it only returns #REF! when there's a true range
reference error.


My *EXACT* formula is =LINEST(Y14:Y50,A14:Q50,1,1)

Is this hardcoded limit documented anywhere? Does Excel 2003 handle a
larger number of channels?

I have a limited understanding of matrix math, but I understand that the
"Normal Equations" involves inverting the [X'X] matrix. To minimize roundoff
errors in the computation, each vector in the X matrix should first be
shifted (centered about its mean). Hopefully LINEST performs this operation
automatically and transparently.


It would be safer (and more nearly true) to assume that it doesn't work
unless you are very careful to precondition the problem so that naive
code can solve it.

I read the article http://support.microsoft.com/kb/828533#kb3 and Microsoft
acknowledges weakness in numerical methods with LINEST in Excel 2002 and
earlier. This article claims that LINEST has been improved in Excel 2003,
using QR decomposition (I'll have to read up to understand that) resulting in
more robust performance. They also advertise
€¢ Better numeric stability (generally smaller round off errors)
€¢ Analysis of collinearity issues


It is pretty scary that earlier versions did not use QR decomposition!

In what form is the result of this "Analysis of collinearity" presented to
the Excel user? What statistic in the matrix returned by LINEST should I look
at to know that the X matrix is "nearing" collinearity.

If the predictors exhibit near collinearity, the solution is to remove
predictors from the group of predictors that are nearly collinear. Is there
an easy way to identify which predictors form a group that is nearly
collinear?


Compute their dot product divided by their magnitude.
Eg vectors A1..An, B1..Bn

=SUMPRODUCT(A1..An,B1..Bn)/SQRT(SUMPRODUCT(A1..An,A1..An)*SUMPRODUCT(B1..Bn,B 1..Bn))

This is zero if they are exactly orthogonal (good thing) or +/- 1 if
they are exactly collinear (very very bad). The closer the predictors
are to being collinear the more unstable the matrix problem becomes.

There are devious ways to fit orthogonal polynomials reliably in Excel
if you really need to do it. Allowing 16 general predictors to be used
without any defensive coding is very optimistic of them!

Fitting general vectors to data is frought with difficulties because of
the intrinsic numerical instability in the Excel algorithms. You must
always check that the fitted model reproduces your data to within the
claimed level of residual error. Trust nothing where LINEST is
concerned. The 2003 version is better but still not right.

Regards,
Martin Brown
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Seed numbers for random number generation, uniform distribution darebo Excel Discussion (Misc queries) 3 April 21st 23 09:02 PM
maximum number of worksheets Shooter Excel Worksheet Functions 8 July 1st 06 06:38 AM
what is maximum number of colums in worksheet? [email protected] Excel Discussion (Misc queries) 3 February 8th 05 01:46 AM
how to increase maximum number of columns in excel 2003 [email protected] Excel Discussion (Misc queries) 1 January 16th 05 09:13 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 06:14 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"