Formula to get a ranking based on three columns

[hostmd]

So I need a formula to get the ranking for employee, based on three categories: MPOR (most important), Cleanliness Rate, and lastly by attendance rate).

I am currently using the formula: =RANK.EQ(P3,P$3:P$13,1)+SUMPRODUCT((P$3:P$13=P3)*(R$3:R$13>R3))+SUMPRODUCT((P$3:P$13=P3)*(R$3:R$13=R3)*(Q$3:Q$13>Q3)Column)
Column P=MPOR Data
Column Q=Attendance
Column R= Cleanliness

As of now, if i change number under Column Q and R, it does not affect the rank number. It seems only Column P and the value in those boxes affects the ranking?


Attachment 855172

[Gregb11]

It would be much easier for us if you attached a sample file. Please read the yellow banner at the top of the page to do this. Also, please update your profile with your actual Excel version ("Current" is not a version).

[hostmd]

I use Excel on the web. Excel for the web is part of the Microsoft 365 subscription suite of online apps. It updates to the most current version automatically every time you log in.

Build #16.0.17307.42304

[Gregb11]

In order for the rank to change if you change the values for Attendance or Cleanliness, you have to have 2 or more rows that have the same Combined Avg. If none of the Combined averages match, then they will never change. Say you change the combined avg in row 11 (for Tira) to match Lisa (46.97). You will see the ranks change. Then if you change the Cleanliness value for Tira to be greater than Lisa (say, 4), the ranks change. Also, in order for the rank to change if you change the Attendance rate is you need to have the cleanliness values match as well. So change the Cleanliness for Tira to 3 (so it matches Lisa). Now change the Attendance rate to a higher number than Lisa (say 80%), the ranks change.
Maybe if you explain in detail how you want the rankings to work, we can give you a formula that will work.

[hostmd]

In order for the rank to change if you change the values for Attendance or Cleanliness, you have to have 2 or more rows that have the same Combined Avg. If none of the Combined averages match, then they will never change. Say you change the combined avg in row 11 (for Tira) to match Lisa (46.97). You will see the ranks change. Then if you change the Cleanliness value for Tira to be greater than Lisa (say, 4), the ranks change. Also, in order for the rank to change if you change the Attendance rate is you need to have the cleanliness values match as well. So change the Cleanliness for Tira to 3 (so it matches Lisa). Now change the Attendance rate to a higher number than Lisa (say 80%), the ranks change.
Maybe if you explain in detail how you want the rankings to work, we can give you a formula that will work.

Thank you for explaining it! My goal here is to have their overall ranking based on those three factors. I’m not sure i explained it in a clearly manner. But I would like their overall ranking weighted on those 3 categories: their mpor, their attendance and their cleanliness score. Business hrs are tight right now and this would help me schedule accordingly

[Gregb11]

You're not giving enough detail. First, is the Combined Average better if higher? I assume the higher the attendance rate the better. Is it the same for the Cleanliness?
Why don't you rank all 3 and add them together (using a factor) (instead of using SUMPRODUCT). You don't say how you want to weight the rankings of each category. So lets say EXCEL was out of the picture and you had to do it by hand, what would you do? (Explain the process in words).

[hostmd]

You're not giving enough detail. First, is the Combined Average better if higher? I assume the higher the attendance rate the better. Is it the same for the Cleanliness?
Why don't you rank all 3 and add them together (using a factor) (instead of using SUMPRODUCT). You don't say how you want to weight the rankings of each category. So lets say EXCEL was out of the picture and you had to do it by hand, what would you do? (Explain the process in words).

Combine Avg would be better if lower, attendance rate AND cleanliness if higher. Ideally I would like for all three to weight equally when ranking, but if not. Then combine average to have the most weight followed by cleanliness and lastly attendance. How would I do that? I apologize I’m not too savy on excel

[Gregb11]

Well, 1 way is to rank each category and add them up and the one with the lowest ranking wins.
=RANK.EQ([@[COMBINED AVG.]],[COMBINED AVG.],1)+RANK.EQ([@[ATTENDANCE RATE]],[ATTENDANCE RATE],0)+RANK.EQ([@[Cleanliness AVG.]],[Cleanliness AVG.],0)

Another way is to give them points for their scores that tries to even out the categories. In other words, if your Combined Avg can be between 1 and 100, and your Attendance rates between 1% and 100%, and the Cleanliness avg from 1 to 5, you could:
1. Combined Avg - subtract it from 100 (since lower is better, this would switch the scores).
2. Attendance rate - multiply the number by 100 (to get rid of the %)
3. Multiply the number by 20 (to get a max of 100).

Add up these scores and the highest number wins.

File attached showing both options.

[josephteh]

Continuing on the good work by Gregb11, I have added 3 attributes/fields to give flexibility in the scoring computation:

1. Weightage in B1:D1 - the weightage to be given for each of the scores
2. Highest possible score in B2:D2 - I am taking the maximum of available scores as the highest possible score; if the maximum score for Combined Avg. is 100 and Cleanliness Avg. is 5, then put 100 in cell B2 and 5 in cell D2
3. Lowest possible score in B3:D3 - I am taking the minimum of available scores as the lowest possible score; if the lowest possible scores for all is 0 or 0%, then key in 0 in B3:D3
4. Recompute percentage score for Combined Avg. in E5=(B$2-[@[COMBINED AVG.]])/(B$2-B$3), copy down
5. Recompute percentage score for Attendance Rate in F5=([@[ATTENDANCE RATE]]-C$3)/(C$2-C$3), copy down
6. Recompute percentage score for Cleanliness Avg. in G5=([@[Cleanliness AVG.]]-D$3)/(D$2-D$3), copy down
7. Weighted Total Score in H5=SUMPRODUCT(Table26[@[COMBINED AVG. SCORE]:[Cleanliness AVG SCORE]],$B$1:$D$1), copy down
8. Ranking in I5=RANK.EQ([@[Weighted Total Score]],[Weighted Total Score]), copy down

[kvsrinivasamurthy]

I am not clear. I have used attendance and Cleanliness add to Combined average.
Try. In F2

Please Login or Register  to view this content.