XL: Using Noncontiguous Ranges in Array Functions

ID: Q79625

The information in this article applies to:

Microsoft Excel 97 for Windows
Microsoft Excel for Windows 95, version 7.0
Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 5.0
Microsoft Excel for OS/2, versions 2.2, 2.21, 3.0

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 example below 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 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)

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 (CTRL+SHIFT+ENTER):


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

REFERENCES

"Function Reference," version 4.0, pages 254-258

"User's Guide 2," version 4.0, pages 159-161

"Microsoft Excel Function Reference," version 3.0, pages 138-141

"Microsoft Excel User's Guide," version 3.0, pages 282-284

Additional query words: non-contiguous contiguous adjacent nonadjacent

Keywords : kbdta
Version : WINDOWS:2.2,3.0,4.0,5.0,7.0,97; MACINTOSH:2.0,3.0,4.0,5.0
Platform : WINDOWS
Issue type : kbhowto