Linest function excel with blank cells

Question

Dear all,

I have a question about the linest function in excel.

I want to use this function in order to estimate a and b in following equation y=a+bx, for 2300 different rows.

However, some data is missing and i was wondering if it would be possible to let excel skip the empty cells automatically, and thus

do not give #value as outcome. Thus that excel uses the data that is available..

Is there a solution for this problem or do i have do this manually, by selecting each x and y manually for

all the 2300 entry's. I can not belief that there is not a solution for this problem...

Hope you can help..

Answer 1

It has to be a single contiguous array.  So, individually selecting 2300 elements, even if done, would still not work.

You will have to remove the missing rows.

It shouldn't be that hard.  Add a new temporary column with the formula =ROW() in it.  Copy this column and paste back as values only.

Now, sort the data on the X values.  All the empty cells will be together.  Delete these rows.

Do the same for the Y values.

Finally, sort on the new column.  This will return the data to the original sequence.  Delete this temporary column.

You can even record a macro doing the above and use it if you have to deal with this issue repeatedly.

Finally, I imagine one could write a wrapper function for LINEST to exclude empty cells or cells with error.