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

1. ## 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.

Pedro

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

Originally Posted by DILIPandey
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. ## Re: IF Statement Within different Rows

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. ## 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

6. ## 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

7. ## Re: IF Statement Within different Rows

@ XOR LX ...

Brilliant ...

Cheers

James

8. ## 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?

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

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

Cheers