weighting attrition

[ballalos]

I am stuck... I am trying to rank performance and attrition is one of the metrics I am trying to measure. I have a formula for weighting but the problem is the formul actually sees higher attrition as a good thing.

Here is the formula I have:

D4 is the goal: Less than 8%
D5 is actual attrition: 10%

Attritions weight is 25%

Formula is: (D5/$D$4*25%)

Any help would be appreciated.

[shg]

Depend on the result you want.

If you have 8% actual attrition, do you get the full 25%?

What if the actual were 0%?

One possibility is =MIN(1, 2 - D5 / $D$4) * 25%

[ballalos]

Yes, if you have less than 8% attrition you get the full 25% rating. This looks like it worked!

If you have any other suggestions, I am greatly appreciative.

Thanks again!

[shg]

The curve can be anything you want. It's 100% (of whatever the weight) at the target, and then should roll off to zero at some value above the target. So you need to specify two values: the target value ('tgt'), and the value at which you get zero ('bad'). Then the general formula is:

=MAX(0, MIN(1, 1 - (tgt - act) / (tgt - bad) ) ) * wgt