Linest() with non-contiguous cells

    Is it possible to use the linest() function on a non-contiguous range of cells? I am cutting and pasting the cells to be contiguous, which is frightfully tedious.

    It seems that linest() needs a contigous range. Instead of cutting and pasting could you sort the range to move all the empties to the bottom - then redefine the range to exclude them?
    Linest still doesn't like blank cells at the end of its range so you could use OFFSET in your formula to exclude them:


    ie if you sort a6:b100 so the blanks are at the bottom, then this formula will calc linest() on a6:b100 ignoring the blanks.


    =LINEST(OFFSET(A6:A100,0,0,COUNT(A6:A100),1),OFFSET(B6:B100,0,0,COUNT(B6:B100),1),TRUE,FALSE)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login