IF Statement Within different Rows

[Oprichnick]

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

 

[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

 

[Oprichnick]

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

 

[DILIPandey]

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

 

[XOR LX]

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

 

[XOR LX]

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

 

[James006]

@ XOR LX ...

Brilliant ...

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

Cheers

James

:wink:

 

[XOR LX]

Cheers, James!

You don't happen to know what those array constant separators for non-English systems should be, do you?

 

[James006]

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 ... :wink:


HTH

 

[XOR LX]

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

Cheers