Hi there...
I am having a bit of trouble with the formula below which is intended to provide a unique (no duplicates) ranking of Productivity scores (highest to lowest) using another metric as tie breaker as needed (lowest Score is best). The formula is used in cells AM7:AM31 and the tie breaker metric is the rank in cells AW7:AW31. The specific issue is occurring in cells AM17 and AM22, where instead of bumping the rank up by 1 to be 8 and 19 respectively it is bumping the rank up by 2 incorrectly showing 9 and 20 respectively.
=IFERROR(RANK($AL17,$AL$7:$AL$31,0)+SUMPRODUCT(--($AL$7:$AL$31=$AL17),--($AW$7:$AW$31<$AW17)),"")
Below is a sample of the current incorrect outcome as well as the intended correct outcome
I did search through threads, there was lot on Ranking in general...but no this specific problem.
Any suggestion on why this might be happening...?
<colgroup><col width="64" style="width: 48pt;">
<col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;">
<col width="85" style="width: 64pt; mso-width-source: userset; mso-width-alt: 3108;">
<col width="81" style="width: 61pt; mso-width-source: userset; mso-width-alt: 2962;">
<col width="88" style="width: 66pt; mso-width-source: userset; mso-width-alt: 3218;">
<col width="86" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3145;">
<col width="16" style="width: 12pt; mso-width-source: userset; mso-width-alt: 585;">
<col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;">
<col width="85" style="width: 64pt; mso-width-source: userset; mso-width-alt: 3108;">
<col width="81" style="width: 61pt; mso-width-source: userset; mso-width-alt: 2962;">
<col width="88" style="width: 66pt; mso-width-source: userset; mso-width-alt: 3218;">
<col width="86" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3145;">
<tbody>
</tbody>
I am having a bit of trouble with the formula below which is intended to provide a unique (no duplicates) ranking of Productivity scores (highest to lowest) using another metric as tie breaker as needed (lowest Score is best). The formula is used in cells AM7:AM31 and the tie breaker metric is the rank in cells AW7:AW31. The specific issue is occurring in cells AM17 and AM22, where instead of bumping the rank up by 1 to be 8 and 19 respectively it is bumping the rank up by 2 incorrectly showing 9 and 20 respectively.
=IFERROR(RANK($AL17,$AL$7:$AL$31,0)+SUMPRODUCT(--($AL$7:$AL$31=$AL17),--($AW$7:$AW$31<$AW17)),"")
Below is a sample of the current incorrect outcome as well as the intended correct outcome
I did search through threads, there was lot on Ranking in general...but no this specific problem.
Any suggestion on why this might be happening...?
AM | AN | AO | AP | AQ | AM | AN | AO | AP | AQ | ||
1 | |||||||||||
2 | |||||||||||
3 | |||||||||||
4 | |||||||||||
5 | Incorrect Outcome | Correct Outcome | |||||||||
6 | Employee | Productivity Score | Productivity Rank | Handle Time Score | Handle Time Rank | Employee | Productivity Score | Productivity Rank | Handle Time Score | Handle Time Rank | |
7 | Employee 1 | 47.22% | 17 | 392.00 | 13 | Employee 1 | 47.22% | 17 | 392.00 | 13 | |
8 | | | | | | | | | |||
9 | Employee 2 | 86.56% | 7 | 268.37 | 3 | Employee 2 | 86.56% | 7 | 268.37 | 3 | |
10 | Employee 3 | 64.92% | 15 | 393.68 | 14 | Employee 3 | 64.92% | 15 | 393.68 | 14 | |
11 | | | | | | | | | |||
12 | | | | | | | | | |||
13 | Employee 4 | 66.89% | 13 | 329.26 | 8 | Employee 4 | 66.89% | 13 | 329.26 | 8 | |
14 | Employee 5 | 100.33% | 4 | 162.83 | 1 | Employee 5 | 100.33% | 4 | 162.83 | 1 | |
15 | Employee 6 | 108.20% | 3 | 298.14 | 5 | Employee 6 | 108.20% | 3 | 298.14 | 5 | |
16 | Employee 7 | 74.76% | 10 | 389.17 | 12 | Employee 7 | 74.76% | 10 | 389.17 | 12 | |
17 | Employee 8 | 86.56% | 9 | 379.00 | 11 | Employee 8 | 86.56% | 8 | 379.00 | 11 | |
18 | Employee 9 | 80.66% | 9 | 565.81 | 19 | Employee 9 | 80.66% | 9 | 565.81 | 19 | |
19 | Employee 10 | 110.17% | 2 | 311.21 | 7 | Employee 10 | 110.17% | 2 | 311.21 | 7 | |
20 | Employee 11 | 92.47% | 6 | 303.62 | 6 | Employee 11 | 92.47% | 6 | 303.62 | 6 | |
21 | Employee 12 | 72.79% | 11 | 345.98 | 9 | Employee 12 | 72.79% | 11 | 345.98 | 9 | |
22 | Employee 13 | 31.48% | 20 | 524.59 | 18 | Employee 13 | 31.48% | 19 | 524.59 | 18 | |
23 | Employee 14 | 31.48% | 18 | 265.17 | 2 | Employee 14 | 31.48% | 18 | 265.17 | 2 | |
24 | Employee 15 | 64.92% | 14 | 360.51 | 10 | Employee 15 | 64.92% | 14 | 360.51 | 10 | |
25 | Employee 16 | 153.45% | 1 | 449.52 | 16 | Employee 16 | 153.45% | 1 | 449.52 | 16 | |
26 | Employee 17 | 57.05% | 16 | 296.12 | 4 | Employee 17 | 57.05% | 16 | 296.12 | 4 | |
27 | Employee 18 | 68.86% | 12 | 460.91 | 17 | Employee 18 | 68.86% | 12 | 460.91 | 17 | |
28 | Employee 19 | 92.47% | 5 | 406.76 | 15 | Employee 19 | 92.47% | 5 | 406.76 | 15 | |
29 | | | | | | | | | |||
30 | | | |||||||||
31 |