Formula issue

System

This discussion was created from comments split from: Formula won't recognize cell data.

alcapps

I have a similar issue. I have a formula =IF([Result: 1st Result]@row = [Result: 2nd Result]@row), "Pass", "Fail"). If 1st and 2nd result are numeric, the formula works but when text, it does not.

1st Result = 1 & 2nd Result = 1: Pass

1st Result = Negative & 2nd Result = Negative: Invalid Operation

My workaround is to enter - for negative and + for positive, but why won't it recognize matching text (I've copied result 1 to result 2 to be sure there are no extra spaces and get the same outcome.)?

KPH

Hi @alcapps

Not much help, but a second pair of eyes at least.

I would expect that it would match on text that is identical. I just tried and can't replicate what you are seeing. Is there something else going on? You do have an extra parenthesis in the formula, although that would stop it working for any data type and I would expect that to return UNPARSEABLE, so think it is a copy/paste issue not in the real sheet?

alcapps

The complete formula is below. I isolated the piece that I think is the issue in my initial comment.

=IF(AND(CONTAINS("Interface", [Testing Type]@row), [Test Code]@row <> "N/A - Calc", [LRR ID]@row <> "See individual orders", [LRR ID]@row <> "See Components"), IF(AND([1st Result]@row <> "", [3rd Result]@row <> "", [2nd Result]@row <> ""), IF(OR(AND(CONTAINS("<", [2nd Result]@row), [1st Result]@row < VALUE(RIGHT([2nd Result]@row, (LEN([2nd Result]@row) - 1)))), AND(CONTAINS(">", [2nd Result]@row), [1st Result]@row > VALUE(RIGHT([2nd Result]@row, (LEN([2nd Result]@row) - 1)))), [1st Result]@row = "n/a", [3rd Result]@row = "n/a", [2nd Result]@row = "n/a", [2nd Result]@row = [3rd Result]@row), "Pass", "Fail"), "Not Tested"), "")

Where:

Testing Type is Interface

Test Code is not N/A - Calc

LRR ID is not See individual orders nor See components

1st, 2nd and 3rd Result are not blank, do not contain "<" nor ">" and are not n/a

1st, 2nd and 3rd Result are "Negative"

If Results are - or + and match, I get a pass. If Results are numeric and match, I get a pass (no match = Fail). If Results are alpha, the formula errors "INVALID OPERATION".

KPH

Can you share the data in the 1st, 2nd, and 3rd Result columns? I'm trying to understand what is being fed into the formula.