1
$\begingroup$

I have a bunch of risk scores out of 100 (there are a few 1000 of them). The issue is they all fall within 42 and 66 - but for the most part they are normally distributed (non-expert analysis).

I was looking for an excel formula that will stretch out the scores. Essentially ignoring 0-20 and 81-100. But still ahving a score out of 100 (for simplicity for end users looking at any visuals).

Essentially I am trying to spread out the scores - while keeping a score out of 100. I've made a very accurate interpretation in msPaint :)

Thank for your help.

enter image description here

$\endgroup$

1 Answer 1

1
$\begingroup$

On your diagram it appears that Risk and Impact have different units and different numeric ranges. The usual way to put disparate continuous variables on the same footing is to turn them into Z-scores. Subtract the sample mean, then divide by the sample's standard deviation.

Now you have values centered on zero, and the unit interval takes you up to examples that are 1 standard deviation from the mean. Pick a cutoff, perhaps ± 2 (or 3) sigma. Then it's just a matter of scaling the range -2 .. 2 into the chosen range of 0 .. 100. So add 2 (giving 0 .. 4) and multiply by 25. You can clip (or discard) the handful of outlier examples falling outside that range.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.