RANK(SUMPRODUCT Unique Ranking With Tie Breaker Problem

MrJoshua

New Member
Joined
Jun 29, 2014
Messages
9
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...?


[TABLE="width: 900"]
<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>[TR]
[TD="width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] "] [/TD]
[TD="width: 72, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"]AM
[/TD]
[TD="width: 85, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"]AN
[/TD]
[TD="width: 81, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"]AO
[/TD]
[TD="width: 88, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"]AP
[/TD]
[TD="width: 86, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"]AQ
[/TD]
[TD="width: 16, bgcolor: transparent"][/TD]
[TD="width: 72, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"]AM
[/TD]
[TD="width: 85, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"]AN
[/TD]
[TD="width: 81, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"]AO
[/TD]
[TD="width: 88, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"]AP
[/TD]
[TD="width: 86, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"]AQ
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: right"]3[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: right"]4[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: right"]5[/TD]
[TD="bgcolor: transparent, colspan: 2"]Incorrect Outcome[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, colspan: 2"]Correct Outcome[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: right"]6[/TD]
[TD="width: 72, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C4BD97]#C4BD97[/URL] "]Employee[/TD]
[TD="width: 85, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C4BD97]#C4BD97[/URL] , align: center"]Productivity Score
[/TD]
[TD="width: 81, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDD9C4]#DDD9C4[/URL] , align: center"]Productivity Rank
[/TD]
[TD="width: 88, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C4BD97]#C4BD97[/URL] , align: center"]Handle Time Score
[/TD]
[TD="width: 86, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDD9C4]#DDD9C4[/URL] , align: center"]Handle Time Rank
[/TD]
[TD="width: 16, bgcolor: transparent"][/TD]
[TD="width: 72, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C4BD97]#C4BD97[/URL] "]Employee[/TD]
[TD="width: 85, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C4BD97]#C4BD97[/URL] , align: center"]Productivity Score
[/TD]
[TD="width: 81, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDD9C4]#DDD9C4[/URL] , align: center"]Productivity Rank
[/TD]
[TD="width: 88, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C4BD97]#C4BD97[/URL] , align: center"]Handle Time Score
[/TD]
[TD="width: 86, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDD9C4]#DDD9C4[/URL] , align: center"]Handle Time Rank
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: right"]7[/TD]
[TD="bgcolor: transparent"]Employee 1[/TD]
[TD="bgcolor: transparent, align: center"]47.22%
[/TD]
[TD="width: 81, bgcolor: transparent, align: center"]17
[/TD]
[TD="bgcolor: transparent, align: center"]392.00
[/TD]
[TD="width: 86, bgcolor: transparent, align: center"]13
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Employee 1[/TD]
[TD="bgcolor: transparent, align: center"]47.22%
[/TD]
[TD="width: 81, bgcolor: transparent, align: center"]17
[/TD]
[TD="bgcolor: transparent, align: center"]392.00
[/TD]
[TD="width: 86, bgcolor: transparent, align: center"]13
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: right"]8[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]
[/TD]
[TD="width: 81, bgcolor: transparent"]
[/TD]
[TD="bgcolor: transparent"]
[/TD]
[TD="width: 86, bgcolor: transparent"]
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]
[/TD]
[TD="width: 81, bgcolor: transparent"]
[/TD]
[TD="bgcolor: transparent"]
[/TD]
[TD="width: 86, bgcolor: transparent"]
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: right"]9[/TD]
[TD="bgcolor: transparent"]Employee 2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF66]#FFFF66[/URL] , align: center"]86.56%
[/TD]
[TD="width: 81, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF66]#FFFF66[/URL] , align: center"]7
[/TD]
[TD="bgcolor: transparent, align: center"]268.37
[/TD]
[TD="width: 86, bgcolor: transparent, align: center"]3
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Employee 2[/TD]
[TD="bgcolor: transparent, align: center"]86.56%
[/TD]
[TD="width: 81, bgcolor: transparent, align: center"]7
[/TD]
[TD="bgcolor: transparent, align: center"]268.37
[/TD]
[TD="width: 86, bgcolor: transparent, align: center"]3
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: right"]10[/TD]
[TD="bgcolor: transparent"]Employee 3[/TD]
[TD="bgcolor: transparent, align: center"]64.92%
[/TD]
[TD="width: 81, bgcolor: transparent, align: center"]15
[/TD]
[TD="bgcolor: transparent, align: center"]393.68
[/TD]
[TD="width: 86, bgcolor: transparent, align: center"]14
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Employee 3[/TD]
[TD="bgcolor: transparent, align: center"]64.92%
[/TD]
[TD="width: 81, bgcolor: transparent, align: center"]15
[/TD]
[TD="bgcolor: transparent, align: center"]393.68
[/TD]
[TD="width: 86, bgcolor: transparent, align: center"]14
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: right"]11[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]
[/TD]
[TD="width: 81, bgcolor: transparent"]
[/TD]
[TD="bgcolor: transparent"]
[/TD]
[TD="width: 86, bgcolor: transparent"]
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]
[/TD]
[TD="width: 81, bgcolor: transparent"]
[/TD]
[TD="bgcolor: transparent"]
[/TD]
[TD="width: 86, bgcolor: transparent"]
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: right"]12[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]
[/TD]
[TD="width: 81, bgcolor: transparent"]
[/TD]
[TD="bgcolor: transparent"]
[/TD]
[TD="width: 86, bgcolor: transparent"]
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]
[/TD]
[TD="width: 81, bgcolor: transparent"]
[/TD]
[TD="bgcolor: transparent"]
[/TD]
[TD="width: 86, bgcolor: transparent, align: center"]
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: right"]13[/TD]
[TD="bgcolor: transparent"]Employee 4[/TD]
[TD="bgcolor: transparent, align: center"]66.89%
[/TD]
[TD="width: 81, bgcolor: transparent, align: center"]13
[/TD]
[TD="bgcolor: transparent, align: center"]329.26
[/TD]
[TD="width: 86, bgcolor: transparent, align: center"]8
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Employee 4[/TD]
[TD="bgcolor: transparent, align: center"]66.89%
[/TD]
[TD="width: 81, bgcolor: transparent, align: center"]13
[/TD]
[TD="bgcolor: transparent, align: center"]329.26
[/TD]
[TD="width: 86, bgcolor: transparent, align: center"]8
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: right"]14[/TD]
[TD="bgcolor: transparent"]Employee 5[/TD]
[TD="bgcolor: transparent, align: center"]100.33%
[/TD]
[TD="width: 81, bgcolor: transparent, align: center"]4
[/TD]
[TD="bgcolor: transparent, align: center"]162.83
[/TD]
[TD="width: 86, bgcolor: transparent, align: center"]1
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Employee 5[/TD]
[TD="bgcolor: transparent, align: center"]100.33%
[/TD]
[TD="width: 81, bgcolor: transparent, align: center"]4
[/TD]
[TD="bgcolor: transparent, align: center"]162.83
[/TD]
[TD="width: 86, bgcolor: transparent, align: center"]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: right"]15[/TD]
[TD="bgcolor: transparent"]Employee 6[/TD]
[TD="bgcolor: transparent, align: center"]108.20%
[/TD]
[TD="width: 81, bgcolor: transparent, align: center"]3
[/TD]
[TD="bgcolor: transparent, align: center"]298.14
[/TD]
[TD="width: 86, bgcolor: transparent, align: center"]5
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Employee 6[/TD]
[TD="bgcolor: transparent, align: center"]108.20%
[/TD]
[TD="width: 81, bgcolor: transparent, align: center"]3
[/TD]
[TD="bgcolor: transparent, align: center"]298.14
[/TD]
[TD="width: 86, bgcolor: transparent, align: center"]5
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: right"]16[/TD]
[TD="bgcolor: transparent"]Employee 7[/TD]
[TD="bgcolor: transparent, align: center"]74.76%
[/TD]
[TD="width: 81, bgcolor: transparent, align: center"]10
[/TD]
[TD="bgcolor: transparent, align: center"]389.17
[/TD]
[TD="width: 86, bgcolor: transparent, align: center"]12
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Employee 7[/TD]
[TD="bgcolor: transparent, align: center"]74.76%
[/TD]
[TD="width: 81, bgcolor: transparent, align: center"]10
[/TD]
[TD="bgcolor: transparent, align: center"]389.17
[/TD]
[TD="width: 86, bgcolor: transparent, align: center"]12
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: right"]17[/TD]
[TD="bgcolor: transparent"]Employee 8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF66]#FFFF66[/URL] , align: center"]86.56%
[/TD]
[TD="width: 81, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF66]#FFFF66[/URL] , align: center"]9
[/TD]
[TD="bgcolor: transparent, align: center"]379.00
[/TD]
[TD="width: 86, bgcolor: transparent, align: center"]11
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Employee 8[/TD]
[TD="bgcolor: transparent, align: center"]86.56%
[/TD]
[TD="width: 81, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=99FFCC]#99FFCC[/URL] , align: center"]8
[/TD]
[TD="bgcolor: transparent, align: center"]379.00
[/TD]
[TD="width: 86, bgcolor: transparent, align: center"]11
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: right"]18[/TD]
[TD="bgcolor: transparent"]Employee 9[/TD]
[TD="bgcolor: transparent, align: center"]80.66%
[/TD]
[TD="width: 81, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF66]#FFFF66[/URL] , align: center"]9
[/TD]
[TD="bgcolor: transparent, align: center"]565.81
[/TD]
[TD="width: 86, bgcolor: transparent, align: center"]19
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Employee 9[/TD]
[TD="bgcolor: transparent, align: center"]80.66%
[/TD]
[TD="width: 81, bgcolor: transparent, align: center"]9
[/TD]
[TD="bgcolor: transparent, align: center"]565.81
[/TD]
[TD="width: 86, bgcolor: transparent, align: center"]19
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: right"]19[/TD]
[TD="bgcolor: transparent"]Employee 10[/TD]
[TD="bgcolor: transparent, align: center"]110.17%
[/TD]
[TD="width: 81, bgcolor: transparent, align: center"]2
[/TD]
[TD="bgcolor: transparent, align: center"]311.21
[/TD]
[TD="width: 86, bgcolor: transparent, align: center"]7
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Employee 10[/TD]
[TD="bgcolor: transparent, align: center"]110.17%
[/TD]
[TD="width: 81, bgcolor: transparent, align: center"]2
[/TD]
[TD="bgcolor: transparent, align: center"]311.21
[/TD]
[TD="width: 86, bgcolor: transparent, align: center"]7
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: right"]20[/TD]
[TD="bgcolor: transparent"]Employee 11[/TD]
[TD="bgcolor: transparent, align: center"]92.47%
[/TD]
[TD="width: 81, bgcolor: transparent, align: center"]6
[/TD]
[TD="bgcolor: transparent, align: center"]303.62
[/TD]
[TD="width: 86, bgcolor: transparent, align: center"]6
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Employee 11[/TD]
[TD="bgcolor: transparent, align: center"]92.47%
[/TD]
[TD="width: 81, bgcolor: transparent, align: center"]6
[/TD]
[TD="bgcolor: transparent, align: center"]303.62
[/TD]
[TD="width: 86, bgcolor: transparent, align: center"]6
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: right"]21[/TD]
[TD="bgcolor: transparent"]Employee 12[/TD]
[TD="bgcolor: transparent, align: center"]72.79%
[/TD]
[TD="width: 81, bgcolor: transparent, align: center"]11
[/TD]
[TD="bgcolor: transparent, align: center"]345.98
[/TD]
[TD="width: 86, bgcolor: transparent, align: center"]9
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Employee 12[/TD]
[TD="bgcolor: transparent, align: center"]72.79%
[/TD]
[TD="width: 81, bgcolor: transparent, align: center"]11
[/TD]
[TD="bgcolor: transparent, align: center"]345.98
[/TD]
[TD="width: 86, bgcolor: transparent, align: center"]9
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: right"]22[/TD]
[TD="bgcolor: transparent"]Employee 13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF66]#FFFF66[/URL] , align: center"]31.48%
[/TD]
[TD="width: 81, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF66]#FFFF66[/URL] , align: center"]20
[/TD]
[TD="bgcolor: transparent, align: center"]524.59
[/TD]
[TD="width: 86, bgcolor: transparent, align: center"]18
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Employee 13[/TD]
[TD="bgcolor: transparent, align: center"]31.48%
[/TD]
[TD="width: 81, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=99FFCC]#99FFCC[/URL] , align: center"]19
[/TD]
[TD="bgcolor: transparent, align: center"]524.59
[/TD]
[TD="width: 86, bgcolor: transparent, align: center"]18
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: right"]23[/TD]
[TD="bgcolor: transparent"]Employee 14[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF66]#FFFF66[/URL] , align: center"]31.48%
[/TD]
[TD="width: 81, bgcolor: transparent, align: center"]18
[/TD]
[TD="bgcolor: transparent, align: center"]265.17
[/TD]
[TD="width: 86, bgcolor: transparent, align: center"]2
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Employee 14[/TD]
[TD="bgcolor: transparent, align: center"]31.48%
[/TD]
[TD="width: 81, bgcolor: transparent, align: center"]18
[/TD]
[TD="bgcolor: transparent, align: center"]265.17
[/TD]
[TD="width: 86, bgcolor: transparent, align: center"]2
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: right"]24[/TD]
[TD="bgcolor: transparent"]Employee 15[/TD]
[TD="bgcolor: transparent, align: center"]64.92%
[/TD]
[TD="width: 81, bgcolor: transparent, align: center"]14
[/TD]
[TD="bgcolor: transparent, align: center"]360.51
[/TD]
[TD="width: 86, bgcolor: transparent, align: center"]10
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Employee 15[/TD]
[TD="bgcolor: transparent, align: center"]64.92%
[/TD]
[TD="width: 81, bgcolor: transparent, align: center"]14
[/TD]
[TD="bgcolor: transparent, align: center"]360.51
[/TD]
[TD="width: 86, bgcolor: transparent, align: center"]10
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: right"]25[/TD]
[TD="bgcolor: transparent"]Employee 16[/TD]
[TD="bgcolor: transparent, align: center"]153.45%
[/TD]
[TD="width: 81, bgcolor: transparent, align: center"]1
[/TD]
[TD="bgcolor: transparent, align: center"]449.52
[/TD]
[TD="width: 86, bgcolor: transparent, align: center"]16
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Employee 16[/TD]
[TD="bgcolor: transparent, align: center"]153.45%
[/TD]
[TD="width: 81, bgcolor: transparent, align: center"]1
[/TD]
[TD="bgcolor: transparent, align: center"]449.52
[/TD]
[TD="width: 86, bgcolor: transparent, align: center"]16
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: right"]26[/TD]
[TD="bgcolor: transparent"]Employee 17[/TD]
[TD="bgcolor: transparent, align: center"]57.05%
[/TD]
[TD="width: 81, bgcolor: transparent, align: center"]16
[/TD]
[TD="bgcolor: transparent, align: center"]296.12
[/TD]
[TD="width: 86, bgcolor: transparent, align: center"]4
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Employee 17[/TD]
[TD="bgcolor: transparent, align: center"]57.05%
[/TD]
[TD="width: 81, bgcolor: transparent, align: center"]16
[/TD]
[TD="bgcolor: transparent, align: center"]296.12
[/TD]
[TD="width: 86, bgcolor: transparent, align: center"]4
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: right"]27[/TD]
[TD="bgcolor: transparent"]Employee 18[/TD]
[TD="bgcolor: transparent, align: center"]68.86%
[/TD]
[TD="width: 81, bgcolor: transparent, align: center"]12
[/TD]
[TD="bgcolor: transparent, align: center"]460.91
[/TD]
[TD="width: 86, bgcolor: transparent, align: center"]17
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Employee 18[/TD]
[TD="bgcolor: transparent, align: center"]68.86%
[/TD]
[TD="width: 81, bgcolor: transparent, align: center"]12
[/TD]
[TD="bgcolor: transparent, align: center"]460.91
[/TD]
[TD="width: 86, bgcolor: transparent, align: center"]17
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: right"]28
[/TD]
[TD="bgcolor: transparent"]Employee 19[/TD]
[TD="bgcolor: transparent, align: center"]92.47%
[/TD]
[TD="width: 81, bgcolor: transparent, align: center"]5
[/TD]
[TD="bgcolor: transparent, align: center"]406.76
[/TD]
[TD="width: 86, bgcolor: transparent, align: center"]15
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Employee 19[/TD]
[TD="bgcolor: transparent, align: center"]92.47%
[/TD]
[TD="width: 81, bgcolor: transparent, align: center"]5
[/TD]
[TD="bgcolor: transparent, align: center"]406.76
[/TD]
[TD="width: 86, bgcolor: transparent, align: center"]15
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: right"]29
[/TD]
[TD="bgcolor: transparent"]
[/TD]
[TD="bgcolor: transparent"]
[/TD]
[TD="width: 81, bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]
[/TD]
[TD="width: 86, bgcolor: transparent"]
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: center"]
[/TD]
[TD="width: 81, bgcolor: transparent, align: center"]
[/TD]
[TD="bgcolor: transparent"]
[/TD]
[TD="width: 86, bgcolor: transparent"]
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: right"]30
[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]
[/TD]
[TD="width: 81, bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="width: 86, bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="width: 81, bgcolor: transparent"]
[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="width: 86, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: right"]31
[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="width: 81, bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="width: 86, bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="width: 81, bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="width: 86, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]
 
Did some more digging and looks like the only real solution is to incorporate ROUND...I applied this to the value itself in Column AN going out to 20 decimals so it doesn't actually alter any of the number that can be visually displayed by extending the decimals displayed. This resolved the Ranking issue so AM17 and AM22 n correctly return 8 and 19 respectively. The issue is tied to the IEEE 754 specification. The link below provides a great deal of info on this and specifically recommends using ROUND to address it.

https://support.microsoft.com/en-us...ithmetic-may-give-inaccurate-results-in-excel
 
Last edited:
Upvote 0

Forum statistics

Threads
1,226,840
Messages
6,193,279
Members
453,788
Latest member
drcharle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top