The information in this article applies to:
SUMMARYMicrosoft 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. WORKAROUNDThe following example shows how to use these workarounds with the LINEST function. ExampleLINEST uses regression analysis to estimate a straight line to fit known data. Here, the known_x's are in a noncontiguous range:
Workaround 1Copy 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 2Enter 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 : kbdta |
Last Reviewed: January 12, 2000 © 2000 Microsoft Corporation. All rights reserved. Terms of Use. |