Weighted Risk Scoring

[wizpeter]

Hi,

I'm trying to show the overall risk scoring for an order based on the required speed for each part of the order, with each part of the order having a number of slow, medium and fast orders. I've tried averages and played around with sumproduct but I just can't get an overall score to to be between 0 and 3 so an overall risk score to the order. Nothing is even coming close.

Part 1 to 4 will have their own risk score based on a risk rating criteria, slow weighting is 1, medium weighting is 2 and fast weighting is 3. These are then used to calculate the overall risk score which is between 1 and 3.

I'm thinking the issue is that my parts don't have a limit to their number and if it were percentages it'd be easier but I need to show the actual number of parts.

Does anyone have any tips or hints they can share? I'm sure the solution sits in a sumproduct but getting it to work is another matter.

For some reason I can't upload the spreadsheet, Uploaded by Moderator, but I've taken a screenshot.

Many thanks.

[Edit by Moderator] - Sample file attached

 

[Hui]

A few questions

1. How can the average be between 0 and 3 when some values are 9?
2. What are the Risk Weightings and how do they impact the averages?
3. Do you know what the answers should be ?

 

[wizpeter]

Hi Hui,

Thank you for commenting.

My idea was to have the overall risk score assigned via a formula (sumproduct?) to a value between 1 and 3.

Low Risk 0 to 1.49
Medium Risk 1.5 to 2.49
High Risk 2.5+

I'm beginning to suspect this might be an issue but as the number of slow, medium and fast component orders per part is in theory unlimited I've not been able to define a scale and a calculation to define it.

For the weightings, I've assigned 1 for slow (no weighting), medium as 2 and high as 3 but whenever I get a result its always coming out as high even if there is just 1 fast component. This would mean for example if there were 3 fast components it would be 3x3 with a weighted score of 9.

For Order 1 I manually worked out the overall score to be 2.5 which fits nicely into my risk scoring, however, if a fast component number is increased it drastically bumps the number up. I think the way I'm trying to get it to work will essentially mean nearly everything is high risk. While I want to show that there are high risk orders I want an proportional spread as much as is possible.