Calculating a staged reduction in severity

burkemrich · 02/27/19

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?

Debbie Sawyer · 02/28/19

Hi

I have had a read of your post and am a little confused. (sorry!) I know it is really tricky to explain a dillema

Taking your example of Score of 30 and Mitigation Score of 5 you want the new column to result in 3?

If you apply your example formula to this scenario you get -20!?

I don't think I am following...

However, if you want to produce a column which removes a percentage of the first number by a percentage driven by the second number you could use a set of nested IF functions to do it.

E.g. if Mitigation 5 = 90% Mitigation 4 = 75% Mitigation 3 = 50% etc your formula could run along the line of

NewScore1 =IF([Mitigation Score]1=5,[Score]1*0.9,IF([Mitigation Score]1=4,[Score]1*0.75,[Score]1*0.5))

Is this what you were after? Sorry if not, happy to discuss further...

Kind regards

Debbie Sawyer Consultant & Training Manager

Smarter Processes mean happy people in successful businesses

burkemrich · 03/01/19

Debbie, that worked great, but in experimenting with the formula it only allows for 3 entries using the IF command. Is that correct?

Other than that it is a great solution!

Debbie Sawyer · 03/02/19

Thank you for your reply!

Glad to hear it is working; no, you are not limited to 3 entries with an IF; you can nest as many as you like/need.

IF functions follow this logic

=IF(Condition,True,False)

If the condition you set is true then do the thing stated after the first comma, otherwise do the thing stated after the second comms (the false part). In the False section, i.e after the second comma you can put another IF function. This is called nesting a function. (In the olden days (90's and early 2000's) we were limited to 7 nested IFS (in Excel) but nowadays you can nest as many as you need. I have several functions in Smartsheet that nest many layers! (Gets a little complicated though!)

The only thing you need to remember is to close each of the Close Function brackets (the ")" at the end of the function). In Smartsheet they are coloured when editing the function, so it is easier to know if you have closed them all!

Thank you for saying this is great and works - I am new to this community page and is great to get good feedback!

Kind regards

Debbie Sawyer Consultant & Training Manager

Smarter Processes mean happy people in successful businesses

Calculating a staged reduction in severity

Comments

Categories