Using IF, AND, OR together

[Nanaia]

Since you are all so wonderful at helping, I have another question.

I'm trying to nest IF, AND, and OR together. I want to tell it IF Q2<12, OR R2<12 AND B2=V are TRUE, then A5, if false then A4. My current working formula is however it doesn't have the AND function in it because I'm not sure how to add it.

=IF(OR(CSV!Q3<12,CSV!R3<12),(REF!$A$2 & REF!B3 & REF!$A$3 & REF!D3 & REF!$A$5 & REF!$A$6 & REF!$A$7 & TEXT(REF!V3,"0.000") & REF!$A$8 & TEXT(REF!W3,"0.000") & REF!$A$9),(REF!$A$2 & REF!B3 & REF!$A$3 & REF!D3 & REF!$A$4 & REF!$A$6 & REF!$A$7 & TEXT(REF!V3,"0.000") & REF!$A$8 & TEXT(REF!W3,"0.000") & REF!$A$9))

Regards,
Nanaia
"Do or do not, there is no try"~Yoda

 

[steve the fish]

You put the OR within the AND so something like:

=IF(AND(OR(Q2<12,R2<12),B2="V"),A5,A4)

 

[Nanaia]

Will that make it so it will only be true if the R2<12 AND B2="V" criteria are met at the same time?

 

[Special-K99]

This shortens your original formula

=REF!$A$2 & REF!B3 & REF!$A$3 & REF!D3 & IF(OR(CSV!Q3<12,CSV!R3<12),REF!$A$4,REF!$A$5) & REF!$A$6 & REF!$A$7 & TEXT(REF!V3,"0.000") & REF!$A$8 & TEXT(REF!W3,"0.000") & REF!$A$9))

since the only difference is the ouput of REF!A4 or REF!A5 which is dependant on your IF condition, ie everything else si the same.

Try this for what you want:

=REF!$A$2 & REF!B3 & REF!$A$3 & REF!D3 & IF(OR(CSV!Q3<12,CSV!R3<12,OR(AND(Q2>=12,R2>=12),B2<>"V")),REF!$A$4,REF!$A$5) & REF!$A$6 & REF!$A$7 & TEXT(REF!V3,"0.000") & REF!$A$8 & TEXT(REF!W3,"0.000") & REF!$A$9))

NOTE: You need to specify VERY CLEARLY what your extra conditions are using brackets.

Do you mean this

IF (Q2<12 OR R2<12) AND B2="V"

or this

IF Q2<12 OR (R2<12 AND B2="V")

 

[Nanaia]

I'm trying to make it so IF Q3<12 is true by itself,
OR
R3<12 AND B2=V are both true at the same time, then A4, if they are all false then A5

 

[Special-K99]

=ref!$a$2&ref!b3&ref!$a$3&ref!d3&if(or(csv!q3<12,csv!r3<12,q2>=12,and(r2>=12,b2<>"v")),ref!$a$4,ref!$a$5)&ref!$a$6&ref!$a$7&text(ref!v3,"0.000")&ref!$a$8&text(ref!w3,"0.000")&ref!$a$9

 

[Nanaia]

If I'm understanding the use of brackets correctly, then it'd be

IF Q2<12 OR (R2<12 AND B2="V")

 

[steve the fish]

Might be this?

=IF(OR(Q3<12,AND(R3<12,B2="V")),A4,A5)

 

[Nanaia]

=ref!$a$2&ref!b3&ref!$a$3&ref!d3&if(or(csv!q3<12,csv!r3<12,q2>=12,and(r2>=12,b2<>"v")),ref!$a$4,ref!$a$5)&ref!$a$6&ref!$a$7&text(ref!v3,"0.000")&ref!$a$8&text(ref!w3,"0.000")&ref!$a$9

looks to be true unless Q2<12.
-------------------------------------------
=IF(OR(Q3<12,AND(R3<12,B2="V")),A4,A5)

looks to be true unless Q2<12

Default values for Q2 & R2 is greater than 12, if Q2 is less than 12, or if R2 is less than 12 while B2 is V, then it's considered special. I am trying to write this so if it's special then a special code comes up.

 

[Nanaia]

Maybe another way to word it would be I only want it to be true if Q2<12, or if (R2<12 AND B2="V") otherwise it's false.