Forum Discussion
=LINEST help
Hello everyone! I am currently trying to make an excel sheet for myself that will automatically calculate all the statistics and stuff when I enter my results. I use =LINEST to determine the slope a...
This is not relevant to many at the moment because it uses modern dynamic arrays but it strikes me that FILTER would offer a solution to missing data. I built an Excel Table 'D' with columns "X" and "Y" to hold the data. The formula
= LINEST(D[Y], D[X])
errors if blank fields are present and gives inappropriate results if blanks are replaced by 0.
Defining named formulas 'X' and 'Y'
= FILTER( D[X], D[Y] )
= FILTER( D[Y], D[Y] )
allows the correct parameter to be determined using
= LINEST( Y, X )