Solved Complicated OR/AND Statement Need Help

December 21, 2016 at 07:02:23
Specs: Windows 7
I have been trying to combine these formulas into 1 formula that can give me a true or false finding so I can then filter with it. I can't seem to get the syntax right as 1 formula, they work individually but I need them in 1 formula.

Can anyone help?

=OR(AND($V7>81,$G7="CIAPPR",$P7="Purchase",$EX=""),$AC7="CRF")
=AND($V7>80,$G7="APPR",$P7="Purchase",$EX7="")
=AND($V7<81,$G7="CIAPPR",$P7="Purchase",$EX7="")
=AND($G7="APPR",$P7="OFFUS",$EX7="")
=AND($G7="APPR",$P7="ONUS",$EX7="")


See More: Complicated OR/AND Statement Need Help

Report •

#1
December 21, 2016 at 07:48:53
✔ Best Answer
To begin with, your first formula does not appear to be correct. Your $EX reference won't work because it refers to a column, not a cell.

=OR(AND($V7>81,$G7="CIAPPR",$P7="Purchase",$EX=""),$AC7="CRF")
                                           ^^^^

Second, it is not clear to me what you are trying to do. Are you trying to OR all of the AND's together? i.e if any of the AND's are TRUE OR the $AC7="CRF" clause is TRUE, then return TRUE?

If that's the case, then (after fixing the cell reference in the first formula) try putting all the other formulas before the closing parenthesis, separated by commas.

Something like this, which shows all the clauses you are checking for TRUE or FALSE. If any of those 6 clauses are TRUE, the entire formula will be TRUE.

=OR(
AND($V7>81,$G7="CIAPPR",$P7="Purchase",$EX7=""),
$AC7="CRF",
AND($V7>80,$G7="APPR",$P7="Purchase",$EX7=""),
AND($V7<81,$G7="CIAPPR",$P7="Purchase",$EX7=""),
AND($G7="APPR",$P7="OFFUS",$EX7=""),
AND($G7="APPR",$P7="ONUS",$EX7="")
)

If that works, I believe that the entire formula can then be shortened since you are checking for the same things multiple times. However, I don't want to go down that path until I'm sure that I understand what you are trying to do.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
December 21, 2016 at 14:38:35
Thanks DerbyDad03 for the quick response, I feel like I owe you since you have helped me all year long. Thank you for all of your help, I wish you happy holidays :-)

Yes the the formula was missing the row number (EX7); the formula you provided did work, I tweaked it a bit to add one more condition but the new condition doesn't seem to be working. I am using a wild card to eliminate anything with that contains the letters "Approval".

=OR(
AND($V34>81,$G34="CIAPPR",$P34="Purchase",$AH34<>"*Approval",$EX34="",$AC34="CRF"),
AND($V34>80,$G34="APPR",$P34="Purchase",$EX34=""),
AND($V34<81,$G34="CIAPPR",$P34="Purchase",$EX34=""),
AND($G34="APPR",$P34="OFFUS",$EX34=""),
AND($G34="APPR",$P34="ONUS",$EX34="")
)


Report •

#3
December 22, 2016 at 07:12:38
Happy Holidays to you and yours also.

You have discovered one of Excel's many quirks. Some functions accept wildcards within quotes, other functions consider a wildcard character within quotes to be a "literal" meaning that Excel is actually looking for the character, e.g. an asterisk.

The <> operator is not recognizing the asterisk as a wild card but as an asterisk.

The COUNTIF function does recognize wildcards, so trying replacing this:

$AH34<>"*Approval"

with this:

COUNTIF($AH34,"*Approval*")=0

or

NOT(COUNTIF($AH34,"*Approval*"))

Those formulas should both return TRUE when Approval is not found in the cell.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

Related Solutions

#4
December 22, 2016 at 08:25:58
Thank you, I actually need it to return false if those characters are there so I changed the 0 to a 1 in the count formula you provided but it is still returning true so something else is wrong. if those characters are in there regardless of any other test it should always come back false.

stand alone this formula returns false
=COUNTIF($AH34,"*Approval*")=1

but incorporated with the whole formula it still returns true:
=OR(
AND($V25>81,$G25="CIAPPR",$P25="Purchase",COUNTIF($AH25,"*Approval*")=1,$EX25="",$AC25="CRF"),
AND($V25>80,$G25="APPR",$P25="Purchase",$EX25=""),
AND($V25<81,$G25="CIAPPR",$P25="Purchase",$EX25=""),
AND($G25="APPR",$P25="OFFUS",$EX25=""),
AND($G25="APPR",$P25="ONUS",$EX25="")
)


Report •

#5
December 22, 2016 at 10:48:19
You have a rather long and complex formula, have you
tried using the Evaluate Formula function from the ribbon?
See here:

https://support.office.com/en-us/ar...

Stepping through the formula should give you an idea of where it is going wrong.

Also, with all the TEXT you are checking, make sure you do not have
any space characters at the end of the text your checking.

The text string "APPR" is NOT the same as 
the text string "APPR "   
    Space Character  ^

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#6
December 22, 2016 at 10:54:14
I'd like to ask one favor:

Please be consistent with the cell references that you use. We don't have a copy of your workbook and therefore often have to set up a worksheet in order to test the formulas.

So far you have referenced Rows 7, 25 and 34. In fact, in your latest response you referenced different rows in your 2 examples. This just adds to the confusion as we try to understand what you are trying to do.

When you say =COUNTIF($AH34,"*Approval*")=1 returns FALSE and then tell us that COUNTIF($AH25,"*Approval*")=1 returns TRUE when used incorporated in the formula, I don't know if you accidentally tested 2 different cells or what.

If you could be consistent with your cell references then we can copy/paste your formulas into the test sheets and not have to keep modifying them.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
December 22, 2016 at 11:30:52
Let's take this piece by piece:

You said:

"I actually need it to return false if those characters are there so I changed the 0 to a 1

and

stand alone this formula returns false
=COUNTIF($AH34,"*Approval*")=1

IF AH34 contains the characters Approval that formula will return TRUE. As soon as COUNTIF sees those characters in a cell it will be set to "1" (TRUE) and since 1=1, the formula will return TRUE.

If that standalone formula is returning FALSE for you, then you don't have Approval in AH34.

The COUNTIF must be tested against 0 for it to return FALSE when Approval is present in the cell.

Think about it: If Approval is in the cell, then the COUNTIF is TRUE. Testing True against 1 will return TRUE because an Excel TRUE = 1. If you test it against a 0 it will return FALSE if Approval is present because TRUE <> 0.

Set up this simple test:

G34 = CIAPPR
P34 = Purchase
V34 = 85
AC34 = CRF
AH34 = Approval
EX34 is blank

This formula, with the COUNTIF tested against 0, will return FALSE because everything is TRUE except for the COUNTIF() = 0 clause.

=AND($V34>81, $G34="CIAPPR", $P34="Purchase",
COUNTIF($AH34,"*Approval*")=0, $EX34="", $AC34="CRF")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
December 22, 2016 at 15:05:19
DerbyDad03, I had the logic backwards and made a rookie mistake with the row numbers not matching up throughout the formula. I used the step through functionality recommended by mmcconaghy and figured out my mistake. I also had to add the countif formula to each AND statement, this may not be the most efficient formula but it works.

Here is the working formula:
=OR(AND($V34>81,$G34="CIAPPR",$P34="Purchase",COUNTIF($AH34,"*Approval*")=0,$EX34="",$AC34="CRF"),AND($V34>80,$G34="APPR",$P34="Purchase",COUNTIF($AH34,"*Approval*")=0,$EX34=""),AND($V34<81,$G34="APPR",$P34="Purchase",COUNTIF($AH34,"*Approval*")=0,$EX34=""),AND($V34<81,$G34="CIAPPR",$P34="Purchase",COUNTIF($AH34,"*Approval*")=0,$EX34=""),AND($G34="APPR",$P34="OFFUS",$EX34=""),AND($G34="APPR",$P34="ONUS",$EX34=""))

Thank you for your patience and thank you for the education :-)

message edited by mecerrato


Report •

Ask Question