Article ID: 181867
Article Last Modified on 9/11/2002
APPLIES TO
- Microsoft Excel 98 for Macintosh
This article was previously published under Q181867
SUMMARY
Microsoft Excel functions that take arrays as arguments, such as
LINEST, LOGEST, IRR, MIRR, MDETERM, NPV, RATE, and XIRR, cannot accept
noncontiguous ranges of data as their array arguments. You must either
copy the ranges to a contiguous area or enter the values as constants,
as the following two workarounds describe.
WORKAROUND
The following example shows how to use these workarounds with the LINEST
function.
Example
LINEST uses regression analysis to estimate a straight line to fit
known data. Here, the known_x's are in a noncontiguous range:
A1: X1 B1: C1: X3 D1: Y1
A2: 2,310 B2: C2: 20 D2: 142,000
A3: 2,333 B3: C3: 12 D3: 144,000
Workaround 1
Copy the data so that it is in a contiguous area of the worksheet and enter
the data as a contiguous range reference. For example, copy the data in
columns A and C into columns E and F and enter the following formula in
cell A5 as an array (COMMAND+RETURN):
=LINEST(D2:D3,E2:F3,,TRUE)
Workaround 2
Enter the data values into the function as array constants, rather than
using a range reference. For example, enter the following formula in cell
A5 as an array (COMMAND+RETURN):
=LINEST(D2:D3,{2310,20;2333,12},,TRUE)
Additional query words: non-contiguous xl98
Keywords: kbinfo KB181867