Handling Noncontiguous Ranges of knownxs in LINEST

One of the few limitations of LINEST is that the range of known_x's must be a contiguous selection (e.g., $A$2:$C$13 in Figure 13-9). Occasionally, you may wish to perform multiple linear regression where the known_x's are not in adjacent rows, and it may not be convenient to rearrange the spreadsheet so as to obtain a contiguous range of known_x's. You can use the custom function Arr to combine separate ranges into a single array. For example, if the ranges of independent variables x\, x2 and x3 were in the ranges A2A13, C2:C13 and E2:E13, respectively, and the dependent variable y in F2:F13, the LINEST expression would be

+3 0

Post a comment