Microsoft KB Archive/79625

= XL: Using Noncontiguous Ranges in Array Functions =

Article ID: 79625

Article Last Modified on 11/16/2006

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Excel 97 Standard Edition

-



This article was previously published under Q79625



For a Microsoft Excel for the Macintosh version of this article, see 181867.



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. This article provides two methods to work around this behavior.



MORE INFORMATION
You must either copy the ranges to a contiguous area or enter the values as constants.

The following example shows how to use these workarounds with the LINEST function.

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

Method 1: Copy the Ranges to a Contiguous Area
Copy the data so 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 (CTRL+SHIFT+ENTER):

=LINEST(D2:D3,E2:F3,,TRUE)

Method 2: Enter the Values as Constants
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 (CTRL+SHIFT+ENTER):

=LINEST(D2:D3,{2310,20;2333,12},,TRUE)

Additional query words: non-contiguous contiguous adjacent nonadjacent XL2000 xl2002 xl97

Keywords: kbhowto KB79625

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.