Using IF, AND, OR together

Nanaia

Board Regular
Joined
Jan 11, 2018
Messages
232
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,216
Office Version
  1. 365
Platform
  1. 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
232
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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,350
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
232
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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,350
=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
232
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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
8,216
Office Version
  1. 365
Platform
  1. Windows
Might be this?

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

Nanaia

Board Regular
Joined
Jan 11, 2018
Messages
232
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
=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
232
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,232
Messages
5,594,962
Members
413,954
Latest member
mrsandy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top