Risk formula

[Keoni]

Does anyone have the guidelines you have to follow when developing formulas
in the calculated value section when creating a new column in the risks
section? I'm trying to create a formula, but it's saying there is a syntax
error.

 

[Keoni]

My customer has a requirement to take data entered from two fields on the
risks page and automatically have it generate another data value. I know how
to do this using a calculated value column if the data entered is a numerical
value and the outcome is a numerical value. However, the data entered is
alphanumeric and the outcome needs to be alphanumeric. For example:

The first column they want is Likelihood. The values for Likelihood are
Remote (20%), Unlikely (40%), Likely (60%), Highly Likely (80%), and Near
Certainty (99%).

The second column is Consequence. The values for Consequence are Minimal,
Slight, Acceptable, Significant, and Unacceptable.

The third column, Priority, is a calculated value field. The values they
want to come up from the calculation from Likelihood * Consequence are High,
Moderate, and Low.

After some discussion I told them that we could put in the description that
20 meant Remote, 40 meant Unlikely, and so and so forth. This would allow me
to also assign a numeric value to the values of Consequence. It was as
follows: 1 is associated with Minimal, 2 was associated with Slight, and up
to 5 being associated with Unacceptable.

Doing this allowed me to get a numeric value. However, the customer was not
satisfied with having a numeric value for Priority. They still wanted Hot,
Moderate, or Low. My question is how do I create a formula to associate the
numeric product value from Likelihood and Consequence to Hot, Moderate, and
Low?

Hope this all makes sense :-(

 

[Geoff Vernon]

Hi Keoni,

Here is what we did. We have 4 columns - Original Likelihood, Original
Consequence, Original Score (OLxOC), Original Rank

Here are the field options and formulas we use:

-Original Likelihood-
Rare
Unlikely
Possible
Likely
Almost Certain

-Original Consequence-
Minor
Important
Serious
Major
Catastrophic

-Original Score-
=IF([Original Likelihood]="Rare",1,IF([Original
Likelihood]="Unlikely",2,IF([Original Likelihood]="Possible",3,IF([Original
Likelihood]="Likely",4,IF([Original Likelihood]="Almost
Certain",5,0)))))*IF([Original Consequence]="Minor",0.5,IF([Original
Consequence]="Important",1,IF([Original
Consequence]="Serious",1.5,IF([Original Consequence]="Major",4,IF([Original
Consequence]="Catastrophic",5,0)))))

-Original Rank-
=IF([Original Score]>16,"Extreme",IF([Original Score]>8,"Very
High",IF([Original Score]>4,"High",IF([Original
Score]>1.5,"Moderate",IF([Original Score]>0,"Low","No Score")))))

This should give you enough to satisfy your customer.

Cheers......Geoff

 

[Nana]

Likelyhood (probability) is always on the scale of 0 -1. Multiply this
by 100 and you get the %. Now the impact is an arbirary scale you come
up with based on your orgainization's risk policies (say 1 - 10). When
you multiply these two scales, you get your risk factor. The higher
this number the more attention the risk will need (and ultimatly cost).
You can then set thresholds based on this. All this info can be
obtained through calculated fields