Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: How to use LOGEST function with data sets that has Blank cell
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2016
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How to use LOGEST function with data sets that has Blank cell

    Hello!

    I was wondering if anyone could help me with following example:

    A1 B1 C1 D1 E1 F1 G1 H1
    121 132 323 123 231 212

    How can do the LOGEST function by using following at cell H1:

    =G1*LOGEST(A1:G1)

    Since there is a blank cell at E1, is there any way to forecast by omitting the cell E1?

    Thanks a lot!
    Jay

  2. #2
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,699
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    2 Thread(s)

    Default Re: How to use LOGEST function with data sets that has Blank cell

    Hi Jay
    Welcome to the board

    Logest(), like Linest(), doesn't allow missing values.

    You have to use just the number values in the range, like:

    =G1*LOGEST(N(OFFSET(A1,,SMALL(IF(ISNUMBER(A1:G1),COLUMN(A1:G1)),ROW(INDIRECT("1:"&COUNT(A1:G1))))-1)))

    This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER and not just ENTER.
    Last edited by pgc01; Mar 11th, 2016 at 06:17 AM.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  3. #3
    New Member
    Join Date
    Mar 2016
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to use LOGEST function with data sets that has Blank cell

    Thanks a lot! It is really helpful!

  4. #4
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,517
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to use LOGEST function with data sets that has Blank cell

    Hi,

    PGC's solution is fine, though it is volatile.

    I prefer the shorter, non-volatile:

    =G1*LOGEST(INDEX(1:1,N(IF(1,MODE.MULT(IF(ISNUMBER(A1:G1),{1;1}*COLUMN(A1:G1)))))))


    Regards
    Advanced Excel Techniques: http://excelxor.com/

  5. #5
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,699
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    2 Thread(s)

    Default Re: How to use LOGEST function with data sets that has Blank cell

    Jay, I'm glad it helped.


    XOR LX, great use of the Mode.Mult to get the list of the columns.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  6. #6
    Board Regular
    Join Date
    Mar 2014
    Posts
    2,436
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to use LOGEST function with data sets that has Blank cell

    Quote Originally Posted by pgc01 View Post
    XOR LX, great use of the Mode.Mult to get the list of the columns.
    But it might be noted that it works only in Excel 2010 and later, when MODE.MULT was introduced.

    I don't believe Jay ever indicated the version of Excel that is used.

  7. #7
    New Member
    Join Date
    Mar 2016
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to use LOGEST function with data sets that has Blank cell

    Thanks a lot!

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

    Default Re: How to use LOGEST function with data sets that has Blank cell

    Hi XOR LX,

    Thanks a lot for the help! I was wondering why did you use IF(1,MODE.MULT(IF(ISNUMBER(A1:G1),{1;1}*COLUMN(A1:G1))). I am not quiet sure about the first IF function as highlighted before.

    Thank you very much!
    Kind Regards,
    Jay

  9. #9
    New Member
    Join Date
    Mar 2016
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to use LOGEST function with data sets that has Blank cell

    Hi XOR LX,

    Thanks a lot for the help! I was wondering why did you use IF(1,MODE.MULT(IF(ISNUMBER(A1:G1),{1;1}*COLUMN(A1:G1))). I am not quiet sure about the first IF function as highlighted before.

    Thank you very much!
    Kind Regards,
    Jay





    Quote Originally Posted by XOR LX View Post
    Hi,

    PGC's solution is fine, though it is volatile.

    I prefer the shorter, non-volatile:

    =G1*LOGEST(INDEX(1:1,N(IF(1,MODE.MULT(IF(ISNUMBER(A1:G1),{1;1}*COLUMN(A1:G1)))))))


    Regards

  10. #10
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,517
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to use LOGEST function with data sets that has Blank cell

    Quote Originally Posted by kayyeah View Post
    I am not quiet sure about the first IF function as highlighted before.
    And quite right, too! For it is indeed rather tautological ((IF(1,... is self-evidently TRUE).

    However, its use in combination with N is quite necessary in this type of construction in order to coerce an array of returns from INDEX. See here:

    INDEX: Returning an array of values EXCELXOR

    Regards
    Advanced Excel Techniques: http://excelxor.com/

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
  •