I would suggest using Contains() instead of long copy pasted list of or statements and possible associative array for value comparison. If you need a formula you might have to use Insert one expression into another using Eval Insert, Eval Expr, Parse, and Substitute to get lists / associative arrays into the formula. This might give some ideas:
Names Default To Here(1);
aa_pc_limits = Associative Array();
aa_pc_limits["MIX 1 A"] = {1, 5};
aa_pc_limits["MIX 2 B"] = {3, 4};
aa_pc_limits["MIX 2 C"] = {2, 5};
aa_pc_limits["MIX 2 D"] = {1, 4};
ntc_valid = {"MTX 1 A", "MIX 2 B", "MIX 2C"};
non_pc_valid = {"MTX 1 IC", "MIX 2 IC", "MIX 3 IC"};
dt = New Table("Untitled",
Add Rows(8),
New Column("Sample Name",
Character,
"Nominal",
Set Values({"NTC", "NTC", "AA", "AA", "AA", "PC", "PC", ""})
),
New Column("Target Name",
Character,
"Nominal",
Set Values(
{"MTX 1 A", "MTX AAAA", "MTX 1 IC", "MTX 1 IC", "AAA", "MIX 1 A", "BBB",
""}
)
),
New Column("CT_numeric",
Numeric,
"Continuous",
Format("Best", 12),
Set Values([., ., 2, 5, ., 2, 5, .])
),
New Column("CT",
Character,
"Nominal",
Set Values({"Undetermined", "", "", "", "", "", "", ""})
),
New Column("Run Validity",
Character,
"Nominal",
Set Values({"", "", "", "", "", "", "", ""})
)
);
dt:Run Validity << Set Formula(
If(:Sample Name == "NTC",
If(Contains(ntc_valid, :Target Name),
If(:CT == "Undetermined",
"Pass";
, // else
"Fail";
)
, // else
"NOT DEFINED. NTC"
)
, :Sample Name != "PC",
If(Contains(non_pc_valid, :Target Name),
If((1 < :CT_numeric < 4),
"Pass"
,
"Fail"
)
,
"NOT DEFINED. NON-PC"
)
, :Sample Name == "PC",
If(Contains(aa_pc_limits, :Target Name),
IfMZ(aa_pc_limits[:Target Name][1] < :CT_numeric < aa_pc_limits[:Target Name][2],
"PASS"
,
"FAIL"
)
,
"NOT DEFINED. PC";
)
, //else
"NOT DEFINED";
);
);
-Jarmo
