Severity calculation

[skynight]

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...

[-Queue-]

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

)

)

[skynight]

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