IF, OR, AND Formulas

[Clarue]

Is it possible to use an IF(OR( formula in addition to AND.

What I'm trying to do is....

IF A2="At Once" AND D2>B2 then give me the value in F2 if False give me " "

BUT

IF A2="Future" and C2>B2 then give me the value in F2 if False give me " "

I'd like to be able to have this calc. all in one formula because what I've been doing is just filtering to At Once and Future separately and doing a simple =IF(D2>B2,(F2),(" ")) and the report already takes enough time on it's own rather not go through the filter and unfilter process if a single formula is possible. Thanks for your help in advance.

[#2]

@Clarue 

Using a table to see what is what

= IF(
  OR(AND([@Type]="At once", [@Available]>[@Requested]),
     AND([@Type]="Future",        [@Exp]>[@Requested])),
  [@Open],
   "")

follows your description. 

[#3 · Accepted solution]

@Clarue 

Yes it's possible to use AND and OR together but what you are trying doesn't needs AND & OR together, since in A2 you are testing two different values. 

Like if A2="At Once" AND D2>B2 should 

If(and(A2>"At Once",D2>B2),F2,0)

Another is 

IF(and(A2="Future", C2>B2),F2,0)

But in both cases you are getting F2 then it should like,,

=IF(AND(A2="At Once",D2>B2),F2,IF(AND(A2="Future",C2>B2),F2,0))

Or you may write this:

=IF(OR(A$2="At Once",A$2="Future"),IF(OR(D$2>B$2,C$2>B$20),F$2,0),0)

• You may adjust cell references in the formula as needed.

[#4]

Thank you so much for your help. This worked perfectly. I think I was making it more difficult than it needed to be. Long work day yesterday.

[#5]

@Clarue 

I took another look at this, and an unusual features of your requirement is that there are only two outturns.  That suggest that only one condition is required.  Building the condition as an array

= IF(
  OR(([@Type]={"Future","At once"})*(Table1[@[Exp]:[Available]]>[@Requested])),
  [@Result1], "")

 or converting the table back to a range

= IF( OR(($A2={"Future","At once"})*($C2:$D2>$B2)), $F2, "")

As an array formula, this may require CSE, I don't remember.  If CSE were required, using SUMPRODUCT instead of OR would work its array magic.

[#6]

@Clarue Glad to help you please keep asking ☺