Blank Cells and LINEST

[Tony Collins]

I have been trying to use LINEST for the analysis of a large sets of data but have just noticed that this function does not allow for empty cells in the data set. Apart from the tredious possibility of removing the blank cells (which is not really possible for the large quantity of data involved) does anybody have any suggestions?

I have tried using the following but have not had any success:
=LINEST(IF(A3:A54=0,"",A3:A54),IF(B3:B54=0,"",B3:B54),,TRUE)

=LINEST(IF(ISNUMBER(A3:A54),A3:A54,""),IF(ISNUMBER(B3:B54),B3:B54,""))

I do not want the formula to take the empty cells as being 0 but to exclude them from the calculation.

All help would be greatly appreciated.

 

[Crones8]

I posted a solution to this very problem not so long ago:
Create a non-contiguous range

Post #9 is the one which is of interest to you here, I believe.

Regards

I know this is an old thread, however I'm struggling with this same problem.

I'm very interested in doing this with only array formulas, and though Macros seem to make it work, I would really like to know if it is possible to do it only with formulas.

I've tried all of the functions suggested in your posts, however, All i get are errors, or undesired results, the idea would be to get (with the same data as the one provided by dnegovan), the same results as the Macros LinestGap in the workbook given in:

Using LinEst() on data with gaps

But only with arrays.

Any help would be appreciated.

Cheers and thank you.