rank calculation

[Shayeebur]

Hi Chandoo,
I am unable to get the correct answer from Rank formula as it skips the rank when the same value is repeated here I have attached the excel sheet. I am trying to get the rank on the basis of Points column as there are some points which are same because of that I am getting the same rank so what is the next criteria is if points are here rank (7) employees are A,X & Y is same for Points (40) then it will look for column Score if column Score is higher among the employee A,X & Y whoever is higher score then as per the highest score rank will be given here Employee A (whose points 40 but Score is 51) will be the 7 rank & employee Y (whose rank is showing 7 but Score is 50) will be 8 rank and employee X will be 9 rank(as his points are same but Score is least among three of them. The second scenario is if the points are same & score is also same then we go for searching as per column “Not ready” if NOT READY data is lesser among the same score then he will be the higher rank here example is Rank 18 whose points are 21 (employees H,P & T) & scores are tied between H & P (98 score) then we search in NOT Ready column whose not ready is lesser between employee H & P here employee H’s not ready is lesser which is 2.8 then rank 18 will be H, rank 19 will be P & rank 20 will be employee T. I tried finding the answer but could not. Please help.

 

[Hui]

You don't say how Points are calculated
What I would do is add a column Adjusted Points
Make this column = Points + Score/100
Then use this as the Ranking Column


If you are still worried about duplicates I would also add the row/10000 to that adjusted Points
eg: G2: =F2+C2/100+ROW()/10000
This will then rank them from Highest Row first

See attached file:

 

[Faseeh]

Hi Shayeebur,

Is it possible to know the max points a person can get and then create a lookup type table to rank on the basis of that table and bypass the rank formula altogher??

 

[Somendra Misra]

Hi,

Try below array formula in G2 and copy down:

=RANK(F2,$F$2:$F$27)+MIN(IF(COUNTIF($F$2:$F$27,F2)>1,IF(F2=$F$2:$F$27,MATCH(C2,LARGE(IF(F2=$F$2:$F$27,$C$2:$C$27),ROW(INDIRECT("1:"&COUNTIF($F$2:$F$27,F2)))),0)-1)))+MIN(IF(COUNTIF($C$2:$C$27,C2)>1,IF(F2=$F$2:$F$27,MATCH(D2,SMALL(IF(F2=$F$2:$F$27,$D$2:$D$27),ROW(INDIRECT("1:"&COUNTIF($F$2:$F$27,F2)))),0)-1)))

Confirm with Ctrl+Shift+Enter.

Regards,

 

[NARAYANK991]

Hi Shayeebur ,

The technique for this is straighforward ; whenever we look at KPIs ( Key Performance Indicators ) , we generally have 2 categories of KPIs :

1. Where more is better
2. Where less is better

An common industrial example would be productivity and accident rates.

When you need a consolidated measure of more than one KPI , use the following technique if all the measures are numeric :

Generate a composite measure by adding measures which are of the first type , and by adding the reciprocal of measures which are of the second type. Use factors of 10 to separate the results. What I mean by this is that if the spread of one factor is from 1 to 100 , while for the second it is from 1 to 10 while for the third it is from 1 to 100 again , you need to ensure that adding 100 from the third factor will not change the composite figure in the overall scheme of things. While it sounds confusing when described , if you see it , you will understand. See the attached file.

Narayan

 

[Shayeebur]

Thanks everyone who have replied out of their busy schedule.

I will reply you all about it is working perfectly or not as currently i am unable to use excel. Thanks once again for your precious time.