IF Statement Within different Rows

Oprichnick

Board Regular
Joined
May 30, 2013
Messages
69
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
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

DILIPandey

Well-known Member
Joined
Jul 25, 2013
Messages
1,336
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

Board Regular
Joined
May 30, 2013
Messages
69
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

Well-known Member
Joined
Jul 25, 2013
Messages
1,336
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

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
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

Well-known Member
Joined
Apr 4, 2009
Messages
3,680

ADVERTISEMENT

@ XOR LX ...

Brilliant ... :)

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

Cheers

James

:wink:
 

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
Cheers, James! :)

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

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
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

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
Thanks, James. Hopefully, then, the default settings are as I gave in this case.

Cheers
 

Watch MrExcel Video

Forum statistics

Threads
1,129,471
Messages
5,636,508
Members
416,919
Latest member
twc2c

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top