Perform Linest function on rows with blank cells excel-CodePudding

Hi I am trying to add a new columns and perform the linest function on rows of data that have blank cells. I only want to perform the function on the cells with values.

If I use the normal =Linest(B2:U2,,,False) I get #Value error.

Any help would be greatly appreciated

CodePudding user response：

Office 365:

=LINEST(FILTER(B2:U2,B2:U2<>""),,,FALSE)

All versions:

=LINEST(B2:INDEX(B2:U2,MATCH(99^99,B2:U2)),,,FALSE)

The first formula will also work if the data is in non-contiguous cells; the second will not. However, judging by your example dataset this is not relevant in your case.