Urgent help - Rank function

[rohanmalhotra]

Hi

i have to prepare a sheet that will give performance ratings to the employees (keeping bell curve in mind). The ratings that needs to be given are 4, 3 & 2. Can anyone help me with it?


Thanks

 

[moonfish]

Assuming you're basing this on a single parameter per employee, and the parameter is normally distributed:

=IF(parameter >= MEAN(params)+STDEV(params),4,IF(parameter <= MEAN(params)-STDEV(params),2,3))

This will give the average 68% of the employees a rating of 3, the top 16% a rating of 4 and the bottom 16% a rating of 2.

That's the best answer I can give you without seeing some sample data.

 

[rohanmalhotra]

Thanks Moonfish.

The parameters are same. its just that the difference is that the top 20% should get a rating of 4, next 60% a rating of 3 and rest get a rating of 2. Also, it would be helpful if you give an example as i am quite new to this.

Thanks once again.

 

[rohanmalhotra]

Any more suggestions with this?

 

[GlennUK]

How about:
=IF(PERCENTRANK(all_perf_ratings,individual_rating)>=0.8,4,IF(PERCENTRANK(all_perf_ratings,individual_rating)< 0.2,2,3))

?

 

[rohanmalhotra]

Hi Glen

this is not comparing scores with one another or is it? correct me if i am wrong.

I will give you an example. There are 6 members in a team and they get scores as below :

A - 85%
B - 95%
C - 79%
D - 86%
E - 79%
F - 85%

i want to give them rating based on there scores. Top 20% - 4, Bottom 20% - 2 and rest of them 3.

Thanks for your help

 

[GlennUK]

Hi Glen

this is not comparing scores with one another or is it? correct me if i am wrong.

Yes, it is comparing scores with one another. Have you tried it?

 

[rohanmalhotra]

yes i did. however, it is giving a rating of 4 to a person with a score of 100% or 50%

 

[GlennUK]

yes i did. however, it is giving a rating of 4 to a person with a score of 100% or 50%

Show the data and formula that give that result.

 

[rohanmalhotra]

I am sorry Glenn i dont know how to attach a file, but i used the same formula you gave me. After minor adjustments, everybody is getting a rating of 2 if they achieve 100%