Using IF, AND, OR together

Nanaia

Board Regular
Joined
Jan 11, 2018
Messages
198
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

Well-known Member
Joined
Oct 20, 2009
Messages
7,778
Office Version
365
Platform
Windows
You put the OR within the AND so something like:

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

Nanaia

Board Regular
Joined
Jan 11, 2018
Messages
198
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

Well-known Member
Joined
Nov 7, 2006
Messages
8,327
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

Board Regular
Joined
Jan 11, 2018
Messages
198
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

Well-known Member
Joined
Nov 7, 2006
Messages
8,327
=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

Board Regular
Joined
Jan 11, 2018
Messages
198
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

Well-known Member
Joined
Oct 20, 2009
Messages
7,778
Office Version
365
Platform
Windows
Might be this?

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

Nanaia

Board Regular
Joined
Jan 11, 2018
Messages
198
=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

Board Regular
Joined
Jan 11, 2018
Messages
198
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.
 

Forum statistics

Threads
1,081,705
Messages
5,360,751
Members
400,595
Latest member
T_Dubs

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top