Assign Ranking to data based on position within Range

Hi, I am Kris, and I have a new project I am working on. What I am trying to to is to assign points to a set of a data based on which percentile within the range each individual falls. I need to assign a rating of 1-10 to a range of data. The purpose of this is to set up a bonus model for employees based on key perfromance indicators.

I want to assign the rating (1-10) based on which percentile the specific cell's data lies. The top 10% get an assigned rating of 10 points the second 10% gets a rating of 9, the third 10% is rated at 8...going down to the last 10% which is assigned a rating of 1. I need to do this in such a way that the formula can addapt to additional employees's data being added. Please not that multiple data points can be a 10, or 9 etc.

Can anybody help me?

Can you please upload a sample of your workbook?

Thanks!

Hi rvasquez

So what I need to acchieve is to assign a rating from 1-10 to ach data point, based on its position within the range. I need to test position within 10% incriments thus, top 10% get a 10 rating, second 10% gets a 9 rating, third 10% gets a 8 rating etc....

Thanks for helping

=TRUNC("data point value" / "max data value" * 10 ,0)

Well, that's basically pseudocode, but it sounds like what you want is, you find out what percent value a data point has, and then turn that tens-columns number into the value?

I already have the value (in percentages), but I want to know in which percentile it falls (taking in to account the whole range) and then assign a rating from 1-10 based on which percentile it falls. Increments of 10% must be used.

So for example:
If Employee # 1 has an average score of 30% wihin a range of various employee percentages, in which percentile does the employee's score fall, and assign a rating corresponding to that tranche. If the score falls in the second to last tranche (using 10% incriments) the assigned rating would be 2, if the score falls in the top 10% of all scores taken into account the employee would be assigned a 10.

It quite a hard thing to do, seen as excel only has a quartile function.

Like attached?

Another explanation that might be usefull is: hypothetically speaking, if there are 200 employees, based on incrimets of 10%, there should be:
20 People in the top 10% - each assigned a rating of 10
20 People in the second 10% - each assigned a rating of 9
20 People in the third 10% - each assigned a rating of 8
20 People in the fourth 10%- each assigned a rating of 7
etc.....until you get to the bottom 10% - each assigned a rating of 1

The 10% tranches should be based on the collective scores of all 200 employees.

So it's based on relative rank, not on the CCT value...

Hi ben_hasel

This seems to work. Yes it is based on the relative-to-others scores. Do you mind explaining the reasons behind the "+1" in the formulas?

Regards