| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF statement in Array Formula's
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))),A BS(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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF statement in Array Formula's
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))),A BS(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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF statement in Array Formula's
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))),A BS(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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF statement in Array Formula's
=IF(OR(A2="",B$2=0),0,A2+SIGN(B$2)*SMALL(IF((WEEKD AY(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((WEEKD AY(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" wrote in message ... 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(I NDIRECT("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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF statement in Array Formula's
Perfect. Thank You Very Much
"Bob Phillips" wrote: =IF(OR(A2="",B$2=0),0,A2+SIGN(B$2)*SMALL(IF((WEEKD AY(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((WEEKD AY(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" wrote in message ... 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(I NDIRECT("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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF statement in Array Formula's
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((WEEKD AY(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((WEEKD AY(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))) |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF statement in Array Formula's
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" wrote in message ... 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((WEEKD AY(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((WEEKD AY(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))) |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF statement in Array Formula's
"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 --- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| defintion of array function | Excel Discussion (Misc queries) | |||
| Creating Array formulas with multiple criteria | Excel Worksheet Functions | |||
| Can someone please explain array formulas to me? | Excel Discussion (Misc queries) | |||
| array formulas | Excel Worksheet Functions | |||
Linear Mode
