CODEWITHSUNDEEP
excelregressionlinear-regressionmissing-data
Julian
Julian

Reputation: 21

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

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

Upvotes: 2

Views: 20622

Answers (3)

Palisek
Palisek

Reputation: 1

I solved the empty cells with my function discard_empty . The function transfers a range to values without empty values and LINEST works.

=LINEST(discard_empty(A1:A9))

Function discard_empty(range_in As Range)
Dim arr_temp() As Variant
Dim arr_new() As Variant
Dim i As Integer
Dim j As Integer
Dim k As Integer
    arr_temp = range_in
    For k = LBound(arr_temp, 1) To UBound(arr_temp, 1)
        For j = LBound(arr_temp, 2) To UBound(arr_temp, 2)
            ReDim Preserve arr_new(i)
            arr_new(i) = arr_temp(k, j)
            i = i + 1
        Next j
    Next k
    arr_temp = arr_new
    Erase arr_new
    j = 0
    For k = LBound(arr_temp) To UBound(arr_temp)
       If arr_temp(k) <> Empty Then
            ReDim Preserve arr_new(j)
            arr_new(j) = arr_temp(k)
            j = j + 1
        End If
    Next k
    discard_empty = arr_new
End Function

Upvotes: 0

The_S.Wan
The_S.Wan

Reputation: 101

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,))

Upvotes: 10

Domenic
Domenic

Reputation: 8114

First, I would suggest that you filter your data to exclude any row containing zeros or blank cells in any of the columns, and then copy the filtered data to another area of your worksheet or a separate worksheet altogether. Then you can simply use LINEST.

Otherwise, you can use the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

=LINEST(INDEX(C:C,N(IF(1,MODE.MULT(IF(MMULT(--(C15:E26=0),TRANSPOSE(COLUMN(C15:E26))^0)=0,ROW(C15:E26)*{1,1}))))),INDEX(D:E,N(IF(1,MODE.MULT(IF(MMULT(--(C15:E26=0),TRANSPOSE(COLUMN(C15:E26))^0)=0,ROW(C15:E26)*{1,1})))),N(IF(1,{1,2}))),1,1)

Hope this helps!

Upvotes: 0

Related Questions