RANK bug: same values get different ranks

[Charles Blaquière]

I have a scoring spreadsheet where column A is the player ranking, B is the
name, and S the point total. I have just noticed that, for a pair of players
with the exact same score, Excel is assigning different ranks. Here's an
excerpt with just those columns:

Rank Player Total
1 JohnF 87
2 Adam 79
3 James 77
3 MarkSu 77
3 Nick 77
6 Tom 76
6 CharlesB 76
8 Jennifer 68
9 Maurice 55
10 Jason 50
11 Kim 48
12 JohnW 43
13 JimW 36
14 Andrew 34
15 Casey 31
16 Paul 22
17 Tim 20
19 Ian 19
18 JoeN 19
20 Eli 18
20 Hal 18
22 Jeremy 13
23 Nancy 12
24 Anthony 9
24 Geoff 9
26 Bob 8
27 Steph 6
28 LizD 4
[Omitted remaining rows up to 52]

As you can see, Excel correctly ranks players tied for 3rd, 6th, 20th, and
24th. But Ian and JoeN, both with 19 points, get ranked differently.

- This is not a "sliding rank range" problem caused by improper formula
definition; The formulae for the first two data rows, for example, are:

=IF(S2=0,"",RANK(S2,S$2:S$52))
=IF(S3=0,"",RANK(S3,S$2:S$52))

- I highlighted both 19-point scores in their cells' formula bar and pressed
F9 to ensure they didn't vary by some small amount. In both cases, Excel
displayed 19 as the calculated value.

- I don't know if this is relevant, but there is another problem on the
sheet, where equal percentage values are shown by Excel to vary in their
least significant digit (0.583333333333333 vs. 0.583333333333334).
Highlighting & calculating parts of the formulae shows the first cell to be
computing 49/84 and the second 35/60; that's equal to 7/12 multiplied by the
same number (5 or 7) in both the numerator and denominator, which gives the
same result. (0.583...3). Perhaps Excel is starting to act screwy on me?

Any hints? Thanks.

 

[Bernie Deitrick]

Charles,

Change the formulas in the total cells (upon which the ranking is based) to
something like:

=ROUND(Oldformula,0)

HTH,
Bernie
MS Excel MVP

 

[Ron Rosenfeld]

I have a scoring spreadsheet where column A is the player ranking, B is the
name, and S the point total. I have just noticed that, for a pair of players
with the exact same score, Excel is assigning different ranks. Here's an
excerpt with just those columns:

Rank Player Total
1 JohnF 87
2 Adam 79
3 James 77
3 MarkSu 77
3 Nick 77
6 Tom 76
6 CharlesB 76
8 Jennifer 68
9 Maurice 55
10 Jason 50
11 Kim 48
12 JohnW 43
13 JimW 36
14 Andrew 34
15 Casey 31
16 Paul 22
17 Tim 20
19 Ian 19
18 JoeN 19
20 Eli 18
20 Hal 18
22 Jeremy 13
23 Nancy 12
24 Anthony 9
24 Geoff 9
26 Bob 8
27 Steph 6
28 LizD 4
[Omitted remaining rows up to 52]

As you can see, Excel correctly ranks players tied for 3rd, 6th, 20th, and
24th. But Ian and JoeN, both with 19 points, get ranked differently.

- This is not a "sliding rank range" problem caused by improper formula
definition; The formulae for the first two data rows, for example, are:

=IF(S2=0,"",RANK(S2,S$2:S$52))
=IF(S3=0,"",RANK(S3,S$2:S$52))

- I highlighted both 19-point scores in their cells' formula bar and pressed
F9 to ensure they didn't vary by some small amount. In both cases, Excel
displayed 19 as the calculated value.

- I don't know if this is relevant, but there is another problem on the
sheet, where equal percentage values are shown by Excel to vary in their
least significant digit (0.583333333333333 vs. 0.583333333333334).
Highlighting & calculating parts of the formulae shows the first cell to be
computing 49/84 and the second 35/60; that's equal to 7/12 multiplied by the
same number (5 or 7) in both the numerator and denominator, which gives the
same result. (0.583...3). Perhaps Excel is starting to act screwy on me?

Any hints? Thanks.

I cannot reproduce your problem with your posted data. I suspect the two
scores are NOT exactly the same on your worksheet.

1. How are the scores calculated? If these are calculated rather than simple
entries of integers, it is likely that the two 19 point scores are NOT exactly
the same.

2. What do you get if you do an equality between the two cells (e.g. in some
cell enter the formula =S19=S20 assuming S19 and S20 are the two cells where
you have these 19 point scores). If you get FALSE, then they are not the same
and the RANK function would rank them differently.

3. The variability that you are seeing in the least significant digit is
inherent in spreadsheets that comply with the IEEE standards and use double
precision math. There have been numerous discussions on these workgroups about
this issue and the matter comes up at least once a week.

4. Rounding is one method of working with these issues.


--ron

 

[Charles Blaquière]

I cannot reproduce your problem with your posted data. I suspect the
two scores are NOT exactly the same on your worksheet.

1. How are the scores calculated? If these are calculated rather
than simple entries of integers, it is likely that the two 19 point
scores are NOT exactly the same.

2. What do you get if you do an equality between the two cells (e.g.
in some cell enter the formula =S19=S20 assuming S19 and S20 are the
two cells where you have these 19 point scores). If you get FALSE,
then they are not the same and the RANK function would rank them
differently.

Yes, the two 19-point scores are in S19 and S20. Entering =S19=S20 in a cell

3. The variability that you are seeing in the least significant
digit is inherent in spreadsheets that comply with the IEEE standards
and use double precision math. There have been numerous discussions
on these workgroups about this issue and the matter comes up at least
once a week.

Thanks for what must be an FAQ. I will simply round the percentages to a few
decimals.

 

[Ron Rosenfeld]

Yes, the two 19-point scores are in S19 and S20. Entering =S19=S20 in a cell
returns TRUE. <chuckle> What now?

That is strange.

How are the numbers in S19 and S20 computed? In other words, what are all the
precedents and values going into these scores.

What happens to the RANKing if you round the values, in S19 and S20, to 12
decimal places?


--ron

 

[Jerry W. Lewis]

Ron Rosenfeld wrote:



Yes, the two 19-point scores are in S19 and S20. Entering =S19=S20 in a cell
returns TRUE. <chuckle> What now?

=S19=S20 proves nothing. Try =(S19-S20) instead.

Excel (and almost all other software) follows the IEEE standard for
double precision storage of floating point numbers. Most decimal
fractions cannot be represented exactly with a terminating binary
fraction (just as 1/3 cannot be represented exactly with a terminating
decimal fraction). The net result is that calculated numbers that you
would expect to be the same may not be exactly the same. Excel tries to
help by including a fuzz factor in some operations, thus if A1 contains
0.3-0.2 and A2 contains 0.1, the cell contents will look the same, even
if formatted to 15 decimal places. Because of Excel's fuzz factor,
=A1-A2 will return 0 and =A1=A2 will return TRUE. But =(A1-A2) will
show the very small difference that correctly occurs between the binary
values, and RANK() will distinguish them.

ROUND() should solve the problem, as has already been suggested.

Jerry

 

[Charles Blaquière]

=S19=S20 proves nothing. Try =(S19-S20) instead.

Yep, that did the trick: it returns 7.105427357601E-15 .

ROUND() should solve the problem, as has already been suggested.

ROUNDing the point toals to zero decimals gave me what I needed. Thanks to
all for your help.