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)