• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Performance Appraisal Tool

B

Ben H

Member
Hi

I have been asked to analyse performance appraisal ratings for around 2000 employees.

The performance appraisal ratings are ...

Outstanding
Exceeds Expectations
Meets Expectations
Needs Improvement
Does Not Meet Requirements

A score of 1 to 5 against these.

Employees joined in different years so some might have ratings for the full 3 years, some for 2 years and some for just one year.

I am looking for an automatic method of scoring these ratings and saying what their average is based on their years of recorded performance ratings.

Ive attached an example workbook and hope to hear a solution.

I am not looking for a VBA solution, as this is part of a very large workbook.

I am looking to see what each employee performs against over the 3 year time period ...


Outstanding
Exceeds Expectations
Meets Expectations
Needs Improvement
Does Not Meet Requirements




Thanks


Ben
 

Attachments

  • merit checker.xlsx
    11.7 KB · Views: 16
Hi Narayan

This looks interesting.

I noted some cells without formula in so I am unable to tell what these are doing.

Other than that I think this is looking good.

Thanks

Ben
 
Hi Ben ,

I have merely moved things around.

The data entry is in the range A2 : D5. This assumes that only 3 years of data will be entered ; if data will be entered for more years , then more columns to the right will need to be used.

The formulae are in columns F through I.

The data in the range L1 through O5 is your rankings in the form of a matrix. I have created a named range RankingsTable for this. What I have done is that I have started with a base value in the first year , and then incremented the base value slightly every year that the ranking is the same , provided the ranking is :

Exceeds Expectations
Outstanding

I think this is reasonable , since if a person is consistently above average , that in itself is to be recognized.

With the average ranking of Meets Expectations , I increased the ranking slightly in the second year as a motivating measure (!) , but if the ranking continues to be average the third year , then I have decreased the ranking back to the base value. Remaining average consistently is not to be appreciated !

For the bottom two rankings , I have decreased the rank values with each year that the ranking remains low.

Of course , the worksheet as it is does not consider the actual data to see whether a low rank repeats for more than a year ; all it is doing is just looking up the value from the table.

If you think it is OK , then we can change the formula to implement this feature.

Narayan
 
Hi Ben ,

As already posted , the range named RankingsTable is just entered data. You need to decide whether this is acceptable or not.

Narayan
 
You must log in or register to reply here.
Back
Top