Feb 10 2021 03:06 PM
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.
Feb 10 2021 03:57 PM
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.
Feb 11 2021 12:00 AM - edited Feb 11 2021 12:06 AM
Solution
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)
Feb 11 2021 04:39 AM
Feb 11 2021 06:37 AM
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.
Feb 11 2021 12:00 AM - edited Feb 11 2021 12:06 AM
Solution
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)