Results 1 to 10 of 10

Thread: IF Statement Within different Rows
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    May 2013
    Posts
    69
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default IF Statement Within different Rows

    Hy all,
    I'm trying to use a Forecast function in which I just want to use the non blank cells. The formula:

    =FORECAST($QG$7;IF(PW8:QF8<>0;PW8:QF8);IF(PW8:QF8<>0;$PW$7:$QF$7))

    In the second block of the Forecast function "IF(PW8:QF8<>0;PW8:QF8)" it returns only the non blank cells;
    However in the third block "IF(PW8:QF8<>0;$PW$7:$QF$7)" it returns me all x values.

    I want to use only the X values to which there are Y values.

    Thanks in advance,

    Pedro

  2. #2
    Board Regular DILIPandey's Avatar
    Join Date
    Jul 2013
    Location
    Dubai
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF Statement Within different Rows

    Hi Oprichnick,

    To consider non zero and non blank cells, you can update your function as if((PW8:QF8<>0)*(PW8:QF8<>""),True,False).


    Regards,
    DILIPandey

  3. #3
    Board Regular
    Join Date
    May 2013
    Posts
    69
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF Statement Within different Rows

    Quote Originally Posted by DILIPandey View Post
    Hi Oprichnick,

    To consider non zero and non blank cells, you can update your function as if((PW8:QF8<>0)*(PW8:QF8<>""),True,False).


    Regards,
    DILIPandey
    Thanks for the quick and useful reply.


    I have another related question... Don't know if I should begin another thread.

    I'm also trying to use the Linest function to obtain a polynomial. I have this formula:

    ={LINEST(IF(Q10:Z10<>"";Q10:Z10;"");IF(Q10:Z10<>"";Q$7:Z$7;"")^{1;2}){


    But again I'm struggling with the blanks... It always returns #VALUE!

    What may be wrong with the above formula?

    Regards

  4. #4
    Board Regular DILIPandey's Avatar
    Join Date
    Jul 2013
    Location
    Dubai
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF Statement Within different Rows

    Glad to help.

    Also, in your new query, I need to know what you are trying to achieve with below part of the formula:-

    ^{1;2}){


    Regards,
    DILIPandey

  5. #5
    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: IF Statement Within different Rows

    Hi.

    Try this array formula**:

    =LINEST(INDEX(10:10,N(IF(1,MODE.MULT(IF(Q10:Z10<>{"";""},COLUMN(Q10:Z10)))))),INDEX($7:$7,N(IF(1,MODE.MULT(IF(Q10:Z10<>{"";""},COLUMN(Q$7:Z$7))))))^{1,2})

    See here for an explanation if interested:

    Criteria with Statistical Functions (GROWTH, LINEST, LOGEST, TREND) EXCELXOR

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

  6. #6
    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: IF Statement Within different Rows

    Actually, it looks like you might be on a non-English version of Excel. Not quite sure how the array constants should be translated for your version. Perhaps:

    =LINEST(INDEX(10:10;N(IF(1;MODE.MULT(IF(Q10:Z10<>{"".""};COLUMN(Q10:Z10))))));INDEX($7:$7;N(IF(1;MODE.MULT(IF(Q10:Z10<>{"".""};COLUMN(Q$7:Z$7))))))^{1\2})

    though I'm afraid I'm not positive.

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

  7. #7
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,443
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)

    Default Re: IF Statement Within different Rows

    @ XOR LX ...

    Brilliant ...

    Love your post on your site ... !!!

    Cheers

    James


  8. #8
    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: IF Statement Within different Rows

    Cheers, James!

    You don't happen to know what those array constant separators for non-English systems should be, do you?
    Advanced Excel Techniques: http://excelxor.com/

  9. #9
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,443
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)

    Default Re: IF Statement Within different Rows

    Hello XOR LX,

    Had to fight with these international issues ...

    It does depend on the local Windows (Not Excel) settings:
    Control Panel > Regional and Language Options > Regional Options

    and they can be customized by each individual ...


    HTH

  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: IF Statement Within different Rows

    Thanks, James. Hopefully, then, the default settings are as I gave in this case.

    Cheers
    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
  •