Formula issue

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

Answers

  • alcapps
    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
    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
    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
    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!