Home |
Search |
Today's Posts |
#1
|
|||
|
|||
RANK bug: same values get different ranks
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, a =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. |
#2
|
|||
|
|||
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 "Charles Blaquière" wrote in message ... 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, a =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. |
#3
|
|||
|
|||
On Tue, 14 Jun 2005 10:59:48 -0400, "Charles Blaquière"
wrote: 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, a =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 |
#4
|
|||
|
|||
Ron Rosenfeld wrote:
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 returns TRUE. <chuckle What now? 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. 4. Rounding is one method of working with these issues. |
#5
|
|||
|
|||
On Tue, 14 Jun 2005 13:02:35 -0400, "Charles Blaquière"
wrote: 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 |
#6
|
|||
|
|||
Charles Blaquière wrote:
Ron Rosenfeld wrote: 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 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 |
#7
|
|||
|
|||
Jerry W. Lewis wrote:
=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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Zero values in a log chart | Charts and Charting in Excel | |||
#N/A Values : Returned by Formulas vs Entered Manually | Charts and Charting in Excel | |||
Rank Formula | Excel Worksheet Functions | |||
Second serie doesn't use X-as values | Charts and Charting in Excel | |||
ranking an row of values | Excel Worksheet Functions |