RANK(SUMPRODUCT Unique Ranking With Tie Breaker Problem

[MrJoshua]

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...?

	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

<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>

 

[MrJoshua]

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