Forum Discussion
IF, OR, AND Formulas
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.
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.
5 Replies
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.
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.
- 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.
Clarue Glad to help you please keep asking ☺
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.
