Ignore zeros (or blanks) in Excel LINEST function with multiple independent variables

Question

I would like to run a multiple (!) factor analysis through linest, again excluding all rows that contain zeros (or if that’s easier blank cells). Is there a way I can do this using the linest functions?

I tried using the following formular, which I have found somewhere else, but would not get anything but #VALUE!.

=LINEST(IF(ISNUMBER(C15:C26);C15:C26;);IF(ISNUMBER(C15:C26);CHOOSE({1;2;3};1;D15:D26;E15:E26););1;1)

FYI – Y values are is column C, and my X values in column D & E.

Thanks in advance! Julian

enter image description here

Solution

Not sure if this has been solved yet but ive been trying to do this myself and found a solution. we can now use the FILTER function. if the range is C15:C26, the following should work

=LINEST(FILTER(C15:C26,C15:C26<>0,))

Comments

[Julian]

I have also found the below linest/offset function, which allows me to exclude cells - however, I only managed to make it work for a single regression ***** =LINEST(OFFSET(C15;MATCH(1;I15:I26;0)-1;0;SUM(I15:I26);1);OF‌​FSET(D15;MATCH(1;I15‌​:I26;0)-1;0;SUM(I15:‌​I26);1);0;1)