I am trying to make up a table that takes one of three hazard risk categories (High, Medium, Low) together with a likelihood probability (Frequent, Probable, Occasional, Remote) to calculate an overall risk (HIGH, MEDIUM, LOW) such that a High risk and Frequent or Probable frequency results in a HIGH overall risk, and a Low risk and Occasional or Remote frequency results in a LOW overall risk (with similar results for other combinations).

I can get it to work once, by defining calculation fields to give a numerical result for each of these parameters. I defined a value list using Pop Up values, then with related calculation fields giving a value of 1 to 3 for each of the risk values (High, Medium, Low) and values of 10, 20, 30 and 40 for the frequency values. I tried to make another calculation field for the result (HIGH, MEDIUM, LOW) that showed, for example, a result of (text) HIGH for a result of 11, 12, and 21; MEDIUM for a result of 13, 22, 23, 31, 32, 41; LOW for a result of 33, 42, 43. This would give me the answers I need when the two entry fields are entered, but all I get when I try to make up a calculation is the first answer (High + Frequent = HIGH). I get nothing for any other combination and the calculation box tells me "there are too many parameters in this function" when I try to develop it past the first formula. It also highlights a semi colon.

The formulas I used: [cSeverity calc =Case(Severity="High";1;Severity="Medium";2;Severity="Low";3)]

[Likelihood calc =Case(Likelihood="Frequent";10;Likelhood= "Probable";20..) etc]

And the final field [Level calc =If(cSeverity+cLikelihood=11;HIGH;cSeverity+cLikelihood=13;MEDIUM...) etc]

If it works, can I ask it to use a range of values instead of writing a calculation for each value? (ie a result of 11, 12, 21 = HIGH; 13, 22, 23, 31, 32, 41 = MEDIUM etc).

Or am I going about it the wrong way? There must be an easy way for this to work...

The error is probably due to quotes not being around the text constants.

Here's what I would use:

Case( not (IsEmpty(Severity) or IsEmpty(Likelihood));

Let( R = cSeverity + cLikelihood;

Case( R < 13 or R = 21; "HIGH"; R < 33 or R = 41; "MEDIUM"; "LOW" )

)

)

Thanks, it works great (one too many brackets is all).