Excel IF formulae 1

[bhp]

Hi does anyone know how I simply add in the following OR criteria to the existing IF statement below it???

=IF((AND(Input!B26="Yes",Input!B22="Yes")),Codelist!A111,"")

=IF(AND(Input!A26<>"",Input!B26="Yes"),IF(Input!$G$8="OPTIMIZED",Codelist!A9,IF(Input!$G$6="Quality",Codelist!A9,IF(Input!$G$7="WFM",Codelist!A51,Codelist!A9)))+IF((AND(Input!B26="Yes",Input!B22="Yes")),Codelist!A111,""),"")

Any help greatly appreciated - James

 

[SkipVought]

Hi,

I'm not going to expend the sweat to figure this out for you. However, when I have complex formulas like this, I simplify by outlining the basic control structure and then substitute the parts, one by one.

For instance, your basic structure may be something like this...
[tt]
=If(and(a<>"",b<>""),if(c=OPT",d,if(f="WFM",g,h),p)
[/tt]
So if you want to add an OR ...
[tt]
=IF((AND(aa="Yes",bb="Yes")),cc,"")
=If(and(a<>"",b<>""),if(c=OPT",d,if(f="WFM",g,h),p)

becomes

=If(OR(IF((AND(aa="Yes",bb="Yes")),cc,"")),and(a<>"",b<>"")),if(c=OPT",d,if(f="WFM",g,h),p)
[/tt]
then substitute to finish.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[buitenhek]

try
IF(AND(A26<>"",B26="yes",B22="yes"),A111,IF(AND(A26<>"",B26="yes"),IF(OR(G8="optimized",G6="quality",NOT(G7="wfm")),A9,A51),""))

Regards
Peter Buitenhek
ProfitDeveloper.com

"Never settle for a job well done...always look for cost cutting measures

 

[bhp]

Hi very sorry shouldhave been as follows

initial if =
=IF(AND(Input!A26<>"",Input!B26="Yes"),IF(Input!$G$8="OPTIMIZED",Codelist!A9,IF(Input!$G$6="Quality",Codelist!A9,IF(Input!$G$7="WFM",Codelist!A51,Codelist!A9))),"")

and the OR I need to add needs to be as follows:
=IF((AND(Input!B26="Yes",Input!B22="Yes")),Codelist!A111,"")

BUT the OR needs to somehow be after (Input!A26<>"",Input!B26="Yes")

Or maybe there is an easier way of doing this!!!!! thanks - James

 

[buitenhek]

if(or(and(a26<>"",b26="yes"),and(b26="yes",b22="yes")),a111,if(and(a26<>"",b26="yes"),if(or(g8="optimized",g6="quality",not(g7="wfm")),a9,a51),"")

Regards
Peter Buitenhek
ProfitDeveloper.com

"Never settle for a job well done...always look for cost cutting measures

 

[bhp]

very cool thanks