IF statement in Array Formula's

[sdg8481]

Hi,

I want to add a conditional If statement to the following Array Formula;

=A2+IF(B$2=0,0,SIGN(B$2)*SMALL(IF((WEEKDAY(A2+SIGN(B$2)*(ROW(INDIRECT("1:"&ABS(B$2)*10))),2)<6)*ISNA(MATCH(A2+SIGN(B$2)*(ROW(INDIRECT("1:"&ABS(B$2)*10))),C$2:C$3,0)),ROW(INDIRECT("1:"&ABS(B$2)*10))),ABS(B$2)))

Whereby if A2 is blank (""), then put blank, otherwise perform the above
formula, how should this formula look, as i've tried doing it severall ways
and struggled on all.

Thansk

[Max]

> Whereby if A2 is blank (""), then put blank, otherwise perform .. formula,

Try adding a front check, viz.:
=IF(A2="","", <your_array_formula>)
Remember to array-enter ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"sdg8481" wrote:
> Hi,
>
> I want to add a conditional If statement to the following Array Formula;
>
> =A2+IF(B$2=0,0,SIGN(B$2)*SMALL(IF((WEEKDAY(A2+SIGN(B$2)*(ROW(INDIRECT("1:"&ABS(B$2)*10))),2)<6)*ISNA(MATCH(A2+SIGN(B$2)*(ROW(INDIRECT("1:"&ABS(B$2)*10))),C$2:C$3,0)),ROW(INDIRECT("1:"&ABS(B$2)*10))),ABS(B$2)))
>
> Whereby if A2 is blank (""), then put blank, otherwise perform the above
> formula, how should this formula look, as i've tried doing it severall ways
> and struggled on all.
>
> Thansk
>
>

[sdg8481]

Hi Thanks, but unfortunately i've already tried that and it doesn't work,
either that or i'm missing something, any other ideas. Thanks


"Max" wrote:

> > Whereby if A2 is blank (""), then put blank, otherwise perform .. formula,
>
> Try adding a front check, viz.:
> =IF(A2="","", <your_array_formula>)
> Remember to array-enter ..
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "sdg8481" wrote:
> > Hi,
> >
> > I want to add a conditional If statement to the following Array Formula;
> >
> > =A2+IF(B$2=0,0,SIGN(B$2)*SMALL(IF((WEEKDAY(A2+SIGN(B$2)*(ROW(INDIRECT("1:"&ABS(B$2)*10))),2)<6)*ISNA(MATCH(A2+SIGN(B$2)*(ROW(INDIRECT("1:"&ABS(B$2)*10))),C$2:C$3,0)),ROW(INDIRECT("1:"&ABS(B$2)*10))),ABS(B$2)))
> >
> > Whereby if A2 is blank (""), then put blank, otherwise perform the above
> > formula, how should this formula look, as i've tried doing it severall ways
> > and struggled on all.
> >
> > Thansk
> >
> >

[Bob Phillips]

=IF(OR(A2="",B$2=0),0,A2+SIGN(B$2)*SMALL(IF((WEEKDAY(A2+SIGN(B$2)*(ROW(INDIR
ECT("1:"&ABS(B$2)*10))),2)<6)*ISNA(MATCH(A2+SIGN(B$2)*(ROW(INDIRECT("1:"&ABS
(B$2)*10))),C$2:C$3,0)),ROW(INDIRECT("1:"&ABS(B$2)*10))),ABS(B$2)))

or perhaps

=A2+IF(OR(A2="",B$2=0),0,SIGN(B$2)*SMALL(IF((WEEKDAY(A2+SIGN(B$2)*(ROW(INDIR
ECT("1:"&ABS(B$2)*10))),2)<6)*ISNA(MATCH(A2+SIGN(B$2)*(ROW(INDIRECT("1:"&ABS
(B$2)*10))),C$2:C$3,0)),ROW(INDIRECT("1:"&ABS(B$2)*10))),ABS(B$2)))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"sdg8481" <[email protected]> wrote in message
news:[email protected]...
> Hi Thanks, but unfortunately i've already tried that and it doesn't work,
> either that or i'm missing something, any other ideas. Thanks
>
>
> "Max" wrote:
>
> > > Whereby if A2 is blank (""), then put blank, otherwise perform ..
formula,
> >
> > Try adding a front check, viz.:
> > =IF(A2="","", <your_array_formula>)
> > Remember to array-enter ..
> > --
> > Max
> > Singapore
> > http://savefile.com/projects/236895
> > xdemechanik
> > ---
> > "sdg8481" wrote:
> > > Hi,
> > >
> > > I want to add a conditional If statement to the following Array
Formula;
> > >
> > >
=A2+IF(B$2=0,0,SIGN(B$2)*SMALL(IF((WEEKDAY(A2+SIGN(B$2)*(ROW(INDIRECT("1:"&A
BS(B$2)*10))),2)<6)*ISNA(MATCH(A2+SIGN(B$2)*(ROW(INDIRECT("1:"&ABS(B$2)*10))
),C$2:C$3,0)),ROW(INDIRECT("1:"&ABS(B$2)*10))),ABS(B$2)))
> > >
> > > Whereby if A2 is blank (""), then put blank, otherwise perform the
above
> > > formula, how should this formula look, as i've tried doing it severall
ways
> > > and struggled on all.
> > >
> > > Thansk
> > >
> > >

[sdg8481]

Perfect. Thank You Very Much

"Bob Phillips" wrote:

> =IF(OR(A2="",B$2=0),0,A2+SIGN(B$2)*SMALL(IF((WEEKDAY(A2+SIGN(B$2)*(ROW(INDIR
> ECT("1:"&ABS(B$2)*10))),2)<6)*ISNA(MATCH(A2+SIGN(B$2)*(ROW(INDIRECT("1:"&ABS
> (B$2)*10))),C$2:C$3,0)),ROW(INDIRECT("1:"&ABS(B$2)*10))),ABS(B$2)))
>
> or perhaps
>
> =A2+IF(OR(A2="",B$2=0),0,SIGN(B$2)*SMALL(IF((WEEKDAY(A2+SIGN(B$2)*(ROW(INDIR
> ECT("1:"&ABS(B$2)*10))),2)<6)*ISNA(MATCH(A2+SIGN(B$2)*(ROW(INDIRECT("1:"&ABS
> (B$2)*10))),C$2:C$3,0)),ROW(INDIRECT("1:"&ABS(B$2)*10))),ABS(B$2)))
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "sdg8481" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Thanks, but unfortunately i've already tried that and it doesn't work,
> > either that or i'm missing something, any other ideas. Thanks
> >
> >
> > "Max" wrote:
> >
> > > > Whereby if A2 is blank (""), then put blank, otherwise perform ..
> formula,
> > >
> > > Try adding a front check, viz.:
> > > =IF(A2="","", <your_array_formula>)
> > > Remember to array-enter ..
> > > --
> > > Max
> > > Singapore
> > > http://savefile.com/projects/236895
> > > xdemechanik
> > > ---
> > > "sdg8481" wrote:
> > > > Hi,
> > > >
> > > > I want to add a conditional If statement to the following Array
> Formula;
> > > >
> > > >
> =A2+IF(B$2=0,0,SIGN(B$2)*SMALL(IF((WEEKDAY(A2+SIGN(B$2)*(ROW(INDIRECT("1:"&A
> BS(B$2)*10))),2)<6)*ISNA(MATCH(A2+SIGN(B$2)*(ROW(INDIRECT("1:"&ABS(B$2)*10))
> ),C$2:C$3,0)),ROW(INDIRECT("1:"&ABS(B$2)*10))),ABS(B$2)))
> > > >
> > > > Whereby if A2 is blank (""), then put blank, otherwise perform the
> above
> > > > formula, how should this formula look, as i've tried doing it severall
> ways
> > > > and struggled on all.
> > > >
> > > > Thansk
> > > >
> > > >
>
>
>

[Max]

Bob, thanks for the help. Glad that did it for the OP. Admit I was lazy in
not testing out the simple front bolt-on. Just wondering why the simple:
=IF(A2="","",...) doesn't get accepted in this instance.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Bob Phillips" wrote:
> =IF(OR(A2="",B$2=0),0,A2+SIGN(B$2)*SMALL(IF((WEEKDAY(A2+SIGN(B$2)*(ROW(INDIR
> ECT("1:"&ABS(B$2)*10))),2)<6)*ISNA(MATCH(A2+SIGN(B$2)*(ROW(INDIRECT("1:"&ABS
> (B$2)*10))),C$2:C$3,0)),ROW(INDIRECT("1:"&ABS(B$2)*10))),ABS(B$2)))
>
> or perhaps
>
> =A2+IF(OR(A2="",B$2=0),0,SIGN(B$2)*SMALL(IF((WEEKDAY(A2+SIGN(B$2)*(ROW(INDIR
> ECT("1:"&ABS(B$2)*10))),2)<6)*ISNA(MATCH(A2+SIGN(B$2)*(ROW(INDIRECT("1:"&ABS
> (B$2)*10))),C$2:C$3,0)),ROW(INDIRECT("1:"&ABS(B$2)*10))),ABS(B$2)))

[Bob Phillips]

I think it would have if it had been correctly interpreted. The thing that I
noticed Max was that the A2 was outside his original IF. I therefore thought
that the way that you wrote it wouldn't return A2 if A2 was not blank but B2
was blank/zero (as per his original), and if he had tried it, he may have
got the syntax wrong. So I thought best to be explicit.

Regards

Bob

"Max" <[email protected]> wrote in message
news:[email protected]...
> Bob, thanks for the help. Glad that did it for the OP. Admit I was lazy
in
> not testing out the simple front bolt-on. Just wondering why the simple:
> =IF(A2="","",...) doesn't get accepted in this instance.
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "Bob Phillips" wrote:
> >
=IF(OR(A2="",B$2=0),0,A2+SIGN(B$2)*SMALL(IF((WEEKDAY(A2+SIGN(B$2)*(ROW(INDIR
> >
ECT("1:"&ABS(B$2)*10))),2)<6)*ISNA(MATCH(A2+SIGN(B$2)*(ROW(INDIRECT("1:"&ABS
> > (B$2)*10))),C$2:C$3,0)),ROW(INDIRECT("1:"&ABS(B$2)*10))),ABS(B$2)))
> >
> > or perhaps
> >
> >
=A2+IF(OR(A2="",B$2=0),0,SIGN(B$2)*SMALL(IF((WEEKDAY(A2+SIGN(B$2)*(ROW(INDIR
> >
ECT("1:"&ABS(B$2)*10))),2)<6)*ISNA(MATCH(A2+SIGN(B$2)*(ROW(INDIRECT("1:"&ABS
> > (B$2)*10))),C$2:C$3,0)),ROW(INDIRECT("1:"&ABS(B$2)*10))),ABS(B$2)))

[Max]

"Bob Phillips" wrote:
> I think it would have if it had been correctly interpreted. The thing that I
> noticed Max was that the A2 was outside his original IF. I therefore thought
> that the way that you wrote it wouldn't return A2 if A2 was not blank but B2
> was blank/zero (as per his original), and if he had tried it, he may have
> got the syntax wrong. So I thought best to be explicit.

Thanks for the thoughts, Bob. Cheers.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---