0

I'm putting together a list to break down stress tests ran on various systems in our department. Without getting into unnecessary details, we conduct random tests using (3) measures that produce (4) results.

This is a simplified explanation of our testing system:

Test A has (3) measures of excellence: >=90%:Exceeds Standards(result); <90%,>=80%:Meets Standards(result); <80%:Fail(result)

Test B has (2) measures of excellence: >=20%:**Overrides Test A data and is a **Fail(result); <=20%:Defers to Test A data results - N/A

Tests will only be measured when >=10 tests are conducted within a month's time. If less than 10 are ran, the data is considered Not Measurable. This is captured in a "Total Tests" column.

They was the data is logged breaks Tests A and B, and Total Tests into their own respective columns. I want to display a calculated column that quickly shows the status of each system after random testing.

Test A and B have interesting dynamics, though. If Test A is ever <80%, it is considered a Fail and if Test B is ever >=20%, it is considered a Fail. Fail will always override any other results. Here are three examples of tests that were ran:

Test A:83% - Test B:13% - Total Tests:23 - Result should be "Meets Standards"

Test A:94% - Test B:3% - Total Tests:13 - Result should be "Exceeds Standards"

Test A:97% - Test B:21% - Total Tests:17 - Result should be "Fail"

Test A:68% - Test B:9% - Total Tests:22 - Result should be "Fail"

Test A:94% - Test B:10% - Total Tests:3 - Result should be "Not Measurable" (Not enough tests conducted)

Hopefully that paints a clear enough picture. If not, I can clarify any points. Below is the formula I've come up with (after A LOT of attempts), but it's still not calculating the data correctly.
I don't understand why it's not working. The only thing I can see is that the “Test B” data forces the calculated column result to “Fail” if the percentage >=3.0%. It does that regardless of where Test A lands >=80%. It makes no sense. I'm really hoping someone help me with this.
Thanks in advance! - J

=IF(AND([Test A]>="90%",[Test B]<"20%",[Total Tests]>=10),"Exceeds Standards",IF(AND([Test A]>="80%",[Test A]<"90",[Test B]<"20%",[Total Tests]>=10),"Meets Standards",IF(AND([Test A]<"80%",[Test B]<"20%",[Total Tests]>=10),"Fail",IF(AND([Test A]>="0%",[Test B]>="0%",[Total Tests]<10),"Not Measurable","Fail"))))
Improve this question

1 Answer 1

Reset to default
0

What kind of fields are Test A and Test B? Their values are being compared to strings in your above example, based on the text like "90%" or "20%." Change those comparisons to be the same type of value as the field. Assuming your fields are type number, shown as percentages, either 0.9 or 90% should work in the equation.

Cheers, Matt

Improve this answer
1
  • Wow. How did I miss that detail. I spent so much time referring to the Test A and Test B columns with quotations because they were single-line text fields. I don't remember why they were ever set up that way. Of course, the third column was a number field. Changing the fields FIXED IT! They're all numbers now. Thanks for seeing that. Appreciate the help!
    – jgett
    Commented Sep 1, 2017 at 4:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.