Excel Weighted Percentage

[Dakker221]

Hi There,

I have question regarding weighted percentages.

Apologies in advance if this isn't in the correct thread.

We currently mark our employees from 0 - 100 in what we call there quality score we then apply a weighting to get our new score:

Currently the weighting for quality 25 so we use

(Employee score/100) * Weighted Score (25)= Weighted Scoring

This is fine but we now have a strange on where for one reason or another we have a test where 300 is the best score you can get a 1000 is the worst

How would I apply a weighted score of 25 to this as the above calculation won't work.

I appreciate any help on this.

Thank you

 

[Eric W]

Welcome to the board.

Maybe:

(1000-score)/7*25

 

[Johnny C]

We need more detail on how the weighting works. Is 25 1/4 of the maximum, i.e. to give you the top quartile?

Assuming it is, you need to subtract 300 from the score and use a weighting score of (1000-300)/4

So, (Employee score/(1000-300)) * Weighted Score ((1000-300)/4)= Weighted Scoring

You could replace 1000-300 with 700 but it's easier to see how you worked it out 6 months down the line when someone looks at it and goes 'Where the heck did 700 come from?'

 

[Dakker221]

We need more detail on how the weighting works. Is 25 1/4 of the maximum, i.e. to give you the top quartile?

Assuming it is, you need to subtract 300 from the score and use a weighting score of (1000-300)/4

So, (Employee score/(1000-300)) * Weighted Score ((1000-300)/4)= Weighted Scoring

You could replace 1000-300 with 700 but it's easier to see how you worked it out 6 months down the line when someone looks at it and goes 'Where the heck did 700 come from?'

Thank you for getting back to me I really appreciate it.

I'm not the greatest with maths so you will have to bare with me. If they score 300 it would result in them getting a weighted scoring of 25 which is the maximum. I hope that makes sense, I'm not the greatest at explaining it.

 

[Eric W]

Since your maximum score is 1000 (the worst you can get), you can switch the values to "the higher, the better" by subtracting the score from 1000. So 1000-1000 = 0. 1000 - 300 = 700. So the scale now is 0-700. To get this to a 0-25 range, divide by 28. (25*28 = 700). So the final formula is:

=(1000-score)/28

Or, to take a note from Johnny C,

=(1000-score)/((1000-300)/25)

where 1000 and 300 are the extreme scores of the test, and 25 is the maximum score you want.

 

[Dakker221]

Since your maximum score is 1000 (the worst you can get), you can switch the values to "the higher, the better" by subtracting the score from 1000. So 1000-1000 = 0. 1000 - 300 = 700. So the scale now is 0-700. To get this to a 0-25 range, divide by 28. (25*28 = 700). So the final formula is:

=(1000-score)/28

Or, to take a note from Johnny C,

=(1000-score)/((1000-300)/25)

where 1000 and 300 are the extreme scores of the test, and 25 is the maximum score you want.

Thank you so much this has sorted it. Life savers

 

[Eric W]

Glad we could help.

 

[Johnny C]

If these scores are likely to change in the future (as they did this time), it might be worth putting the max, min and scale values into cells and referencing the cells in Eric's formula. Then you don't need to worry about the maths in future, just overtype the existing values.

so, if the highest possible score is in cell B2, the lowest possible score is in cell B3 and the scale is in cell B4, the formula would be
($B$2-score)/(($B$2-$B$3)/$B$4)
(Column B as you put the descriptions into Column A)