Formula issue
Answers
-
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.)?
-
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?
-
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".
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 377 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!