Since upgrading from 2002 to 2003 a spreadsheet that worked is now not
working correctly. The formula is not calculating the selected data
correctly. The formula is

=INDEX(LINEST(D12:O16,D37:O41,,FALSE),1)

Excel is having a problem selecting data on multiple rows. A test has been
carried out when the data was moved into one column or one row and it worked.
It appears excel is now only taking into account the first row of data.

Is there an option that needs to be selected for it to work? The help files
were compared between the versions of excel and there is no difference. The
objective of the formula is to calculate a regression line.

If you require more information please ask.

Thank you

Nathan

Hi Nathan,

That's actually kind of a strange formula.

1. It's normal to put what Excel terms the "known_y's" and "known_x's" each
in its own (single) column or, less typically, in its own row, so something
such as this:

=LINEST(D12:D61,E12:E61,,FALSE)

is more usual. However, LINEST handles multi-column, multi-row arrays
correctly. One reason that you usually put the known_y's and known_x's in
individual columns (or rows) is that it's much easier to manage the
one-to-one correspondence between individual records' values.

2. When you specify FALSE as the fourth argument to LINEST, you get only the
intercept and as many coefficients as you have predictors, returned in just
one row of function results. So, with one known_y (the values in D12:O16),
and one known_x (the values in D37:O41), you will get one intercept and one
coefficient. Excel returns those two values in row 1, columns 1 and 2, of
the result array. However, the use of the INDEX function calls for just the
first row (which is appropriate) but not both columns (inappropriate).
You'd need another instance of INDEX, one that specified the 2nd column, to
get both the intercept and the coefficient.

3. Unclear why INDEX is used at all. By array-entering the LINEST function
into a single-row, 2-column range, you get the intercept and coefficient
without involving INDEX. (However, using INDEX does relieve you of the
necessity of array-entering LINEST, so maybe that's it.)

4. Version differences between XL 2002 and 2003 are irrelevant to the
problem you describe.

5. What specifically is the combination of INDEX and LINEST returning, both
"correctly" and "incorrectly"?

C^2
Conrad Carlberg

--
Excel Sales Forecasting for Dummies, Wiley, 2005

"NathanG" wrote in message
...
Since upgrading from 2002 to 2003 a spreadsheet that worked is now not
working correctly. The formula is not calculating the selected data
correctly. The formula is

=INDEX(LINEST(D12:O16,D37:O41,,FALSE),1)

Excel is having a problem selecting data on multiple rows. A test has been
carried out when the data was moved into one column or one row and it
worked.
It appears excel is now only taking into account the first row of data.

Is there an option that needs to be selected for it to work? The help
files
were compared between the versions of excel and there is no difference.
The
objective of the formula is to calculate a regression line.

If you require more information please ask.

Thank you

Nathan