I have one column that shows what a risk is ranked as (a score from 1 to 49 with 49 being the most risky). I am trying to put in another column that lets a ranking for the effectiveness of the mitigation planning (score of 1-5) and then have that drive an auto-calculation of the final score of the Risk. Unfortunately I am approaching this as a straight forward problem but it needs to be more nuanced than that...here is what I came up with:
=Score1 - ([Mitigation Score]1 * 10)
I want something more elegant. I want the function to look at the Score1 value and limit the output so that it can't be larger than the Score1 value. I want the Mitigation Score to be a percentage of the Score1 value. When the function combines them it will show a reduced value.
Example:
Score1 = 30
Mitigation Score=5 which is equivalent to a 90% reduction in the Score1 value. This results in a final score of 3 or a 90% reduction.
Can anyone out there assist with this?
