Ranking in Excel based on a scale of 1-10

[KrisMI]

Hi All

I am designing a emloyee contribution model. I have used 5 key performance indicators to rank the employees. Taking an indicator individually I have used the =Rank function to rank the employee scores relative to each other. Next I take the =Rank score and used a =Trunc function to assign a rating form 1(lowest)-10(highest). I have done this for each KPI individually. Summing the scores(out of 10) of an employee, I come to a final score. Now, the problem is that various of these final scores are of the same amount, and in order to give a final ranking out of 1(lowest)-10(highest), I need to do a =Rank tie break using =if(countif) and base it on some other score.

The tie break I can do and it provides a final ranking with decimal values. Seen as the =Trunc function only rounds down to the nearest integer, it would not provide me with a normally distributed final Rank (1-10).

How do I adapt the =Trunc function to provide me with a solid 1-10rank, without skewing the distribution? Or is there another way.

Please see sample.

Regards

[JBeaucaire]

Not sure I understand how TRUNC matters here? This formula in R3 then copied down seems to give unique rankings:

=RANK($R3, $R$3:$R$89)

[KrisMI]

Thanx for the reply

It seems to work now. When you get the rank(with decimals)you run another rank function on that and then apply the trunc function to bracket the scores into 10& bands. As a fail save for all KPI's follow the same manner of going about it.Please see attached how to go about it. Thanks for the help.