Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

# XL: Using Noncontiguous Ranges in Array Functions

## 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.

↑ Back to the top

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)

↑ Back to the top

## Applies to:

↑ Back to the top

Keywords: KB79625, kbhowto

↑ Back to the top

Article Info
 Article ID : 79625 Revision : 3 Created on : 9/19/2011 Published on : 9/19/2011 Exists online : False Views : 460