Blank Cells and LINEST
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: Blank Cells and LINEST
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2009
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Blank Cells and LINEST

    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.

  2. #2
    Board Regular Phox's Avatar
    Join Date
    Jul 2004
    Location
    Los Angeles, CA
    Posts
    522
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Blank Cells and LINEST

    Enter this as an array formula (Ctrl+Shift+Enter):
    Code:
    =LINEST(IF(A3:A54="",A3,A3:A54),IF(B3:B54="",B3,B3:B543))
    Gladly helping those who are willing to learn and gladly learning from those who are willing to teach.

  3. #3
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Blank Cells and LINEST

    Another possibility:

    =LINEST(N(OFFSET(A3:A54,SMALL(IF(ISNUMBER(A3:A54),ROW(A3:A54)-ROW(A3)),ROW(INDIRECT("1:"&COUNT(A3:A54)))),,1)),N(OFFSET(B3:B54,SMALL(IF(ISNUMBER(B3:B54),ROW(B3:B54)-ROW(B3)),ROW(INDIRECT("1:"&COUNT(B3:B54)))),,1)))

    also with Ctrl+Shift+Enter.
    Microsoft MVP - Excel

  4. #4
    Board Regular Phox's Avatar
    Join Date
    Jul 2004
    Location
    Los Angeles, CA
    Posts
    522
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Blank Cells and LINEST

    Quote Originally Posted by Phox View Post
    Enter this as an array formula (Ctrl+Shift+Enter):
    Code:
    =LINEST(IF(A3:A54="",A3,A3:A54),IF(B3:B54="",B3,B3:B543))
    Econometrics class from years ago is flooding back to me... On second thought, while avoiding the error, this method may skew your results, so you should probably check the answer above, although I'm not sure what his solution is doing.
    Gladly helping those who are willing to learn and gladly learning from those who are willing to teach.

  5. #5
    New Member
    Join Date
    Jun 2009
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Blank Cells and LINEST

    Thank you both very much for the rapid replies.
    However these solutions do not seem to be working, it appears that Phoxs suggestion has the problem of replacing the empty cell with the value in cell A3 ('value if TRUE' in logical test).
    Mr Poulsoms suggestion appears close (even though its complexity baffles me, I don't really understand it), but only appears to work if the cells for corresponding x and y values are BOTH empty.
    In my case the X valuse are constant while the Y values vary, with the Y values frequently containing empty cells or indeed 0.
    I am surprised that EXCEL don't seem to have a means for the function to ignore empty cellls or those containing 0 when carrying out a LINEST calculation.
    I'm actually not too sure if it is possible to regulate this problem with EXCEL.
    Thank you.

  6. #6
    Board Regular Phox's Avatar
    Join Date
    Jul 2004
    Location
    Los Angeles, CA
    Posts
    522
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Blank Cells and LINEST

    Stick around Tony. I'm sure Andrew can alter his formula to accomodate. I doubt he expected only one of the values would be blank.
    Gladly helping those who are willing to learn and gladly learning from those who are willing to teach.

  7. #7
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,824
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Blank Cells and LINEST

    Andrew's formula can be modified as follows...

    Code:
    =LINEST(N(OFFSET(A3:A54,SMALL(IF(B3:B54<>0,ROW(B3:B54)-ROW(B3)),
         ROW(INDIRECT("1:"&COUNT(B3:B54)))),,1)),N(OFFSET(B3:B54,
              SMALL(IF(B3:B54<>0,ROW(B3:B54)-ROW(B3)),ROW(INDIRECT("1:"&
                   COUNT(B3:B54)))),,1)))
    ...confirmed with CONTROL+SHIFT+ENTER. If, however, the so-called empty cells actually contain formula blanks (""), the formula would need to be modified further.

    Edit: Actually, I guess it should be the other way around...

    Code:
    =LINEST(N(OFFSET(A3:A54,SMALL(IF(A3:A54<>0,ROW(A3:A54)-ROW(A3)),
         ROW(INDIRECT("1:"&COUNT(A3:A54)))),,1)),N(OFFSET(B3:B54,
              SMALL(IF(A3:A54<>0,ROW(A3:A54)-ROW(A3)),ROW(INDIRECT("1:"&
                   COUNT(A3:A54)))),,1)))
    Last edited by Domenic; Jun 5th, 2009 at 01:24 PM.

  8. #8
    New Member
    Join Date
    Mar 2013
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Blank Cells and LINEST

    I was able to work Domenic's formula if the cell values were vertically placed. I tried to modified the formula(see below) for cell values placed horizontally, however I keep getting "#Value!". I'm not sure if my formula is correctly modified.

    Code:
    =LINEST(N(OFFSET(B15:K15,,SMALL(IF(B15:K15<>0,COLUMN(B15:K15)-COLUMN(B15)),
    COLUMN(INDIRECT("1:"&COUNT(B15:K15)))),1,)),N(OFFSET(B16:K16,,
    SMALL(IF(B15:K15<>0,COLUMN(B15:K15)-COLUMN(B15)),COLUMN(INDIRECT("1:"&
    COUNT(B15:K15)))),1,)))

  9. #9
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    20,825
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Blank Cells and LINEST

    Some of the ROW functions need to remain as ROW, even for horizontal ranges of data - try this version

    =LINEST(N(OFFSET(B15:K15,,SMALL(IF(B15:K15<>0,COLUMN(B15:K15)-COLUMN(B15)),ROW(INDIRECT("1:"&COUNT(B15:K15)))),1,)),N(OFFSET(B16:K16,,SMALL(IF(B15:K15<>0,COLUMN(B15:K15)-COLUMN(B15)),ROW(INDIRECT("1:"&COUNT(B15:K15)))),1,)))

  10. #10
    New Member
    Join Date
    Mar 2013
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Blank Cells and LINEST

    Perfect.... essentially I am trying to calculate a multiple regression, with the "y" variable having blanks or "-" at times. If the "y" variables has blanks or "-" the regression ignores the calculation for that paired series. I did an independent multiple regression compared to my formula and I get different answers.

    X variables : BQ11:DX12
    Y variables : BQ18:DX18


    =INDEX(LINEST(N(OFFSET(BQ18:DX18,,SMALL(IF(BQ18:DX18<>0,COLUMN(BQ18:DX18)-COLUMN(BQ18)),ROW(INDIRECT("1:"&COUNT(BQ18:DX18)))),1,)),N(OFFSET(BQ11:DX12,,SMALL(IF(BQ18:DX18<>0,COLUMN(BQ18:DX18)-COLUMN(BQ18)),ROW(INDIRECT("1:"&COUNT(BQ18:DX18)))),1,)),TRUE,TRUE),1,1)+INDEX(LINEST(N(OFFSET(BQ18:DX18,,SMALL(IF(BQ18:DX18<>0,COLUMN(BQ18:DX18)-COLUMN(BQ18)),ROW(INDIRECT("1:"&COUNT(BQ18:DX18)))),1,)),N(OFFSET(BQ11:DX12,,SMALL(IF(BQ18:DX18<>0,COLUMN(BQ18:DX18)-COLUMN(BQ18)),ROW(INDIRECT("1:"&COUNT(BQ18:DX18)))),1,)),TRUE,TRUE),1,2)
    ctrl+shift+enter

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •