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"))))