Excel RANK error

[rogerte]

Attached is an excel sheet that adds numbers entered in 2 columns (A :B), sums them in column C, and then uses the RANK function to display order in column D.

Ususally everything is ok, with the ranking, and we get what is expected (e.g. if there are 2 equal second highest values they will both be ranked 2 and the next highest value will be ranked 4.

In the sample attached the 2 equal totals of 25.9 (first group) and 26.6(second group) are assigned different ranks.

The sample this is from is a competition scoring spreadsheet, and has about 50 small competitor groups like this. On the last one this error happened in three of the groups.

The error happens in both Excel 2003 and 2007

Is this a known bug or is there something obvious I am missing?

Thanks

Roger

 

[rogerte]

OOPS,

Sorry about attachment - obviously doesn't like copying files from local PC.

So better enter what is shown here
Columns A and B contain numbers , column C contains sums of A and B, and column D contains rank using formula:

=RANK(C1,$C$1:$C$5,0)

copy and pasted for actual ranges used, and adjusted for different group ranges

In the 2 cases I have on the test sheet the values are
for first group
16.7 9.4
15.4 9.6
16.1 9.8
16.1 9.9
16.2 9.7

and for second group
16.6 10.0
15.9 9.7
15.7 10.3
15.6 9.7
16.4 10.2

In the first group the 3rd and 5th totals are both 25.9, but are given different ranks (3 and 4)

In the second group the first and fifth totals are the same (26.6) but are ranked 1 and 2

Sorry about confusion!

Roger

 

[rogerte]

I've sorted it - it is the SUM(A1:B1) that creates the total in C1 is causing the problems.

using the formula:
=ROUND(SUM(A1:B1),10)
in column C gets over the problem.

Wonder why this rounding error only appears to happen in some cases though?

Cheers

Roger

 

[Jowidog]

Is this the result you are seeing?

16.70 9.40 26.10 1
15.40 9.60 25.00 5
16.10 9.80 25.90 3
16.10 9.90 26.00 2
16.20 9.70 25.90 4


16.60 10.00 26.60 1
15.90 9.70 25.60 4
15.70 10.30 26.00 3
15.60 9.70 25.30 5
16.40 10.20 26.60 2

What is it that you are trying to clarify? Are you looking for a rank order based on the result of the range? By Default the numbers or ranks are ranked in descending order or put another way, Excel automatically searches / ranks values from the highest to lowest. There are three parameters as you know that the function looks for, the number you are looking for, the range you are searching and finally you can supply the third parameter that specifies how Excel should order the values before searching. But what I think you asking is if we have two of the same numbers in this case, 26.60 how does it derive that 1 value is ranked higher than the other? The answer is that Excel handles the issue in much the same way it deals with other duplicates like times in a race and groups them together but the back end logic based on the formula is dictating " an absolute" so it makes the decision to pick the first value based on the rank and range that meets the rank criteria. If you want to reverse the order of presentation or ranking simply include in the syntax a 0 for descending or a 1 for ascending.

HTH

Jim

 

[Jowidog]

One other thing, in 2010 Rank is handled in to different way, one is Ran.EQ which works in the same wasy as RANK(). Assigning each value or rank a different ranking even if two scores are the same. What you may be looking for is Rank.AVG() which assigns rank for tied values. So iff two things tie for the score, they average the Rank.

Make sense?

HTH

From the sands of the desert, somewhere where they are NOT real happy with us right now....

Jim

 

[lionelhill]

I think you've run into a floating point issue. You have typed in a decimal number, and Excel is storing it as a binary floating point number, which is a (very very good) approximation. When translated into binary, there is no guarantee that 16.1+9.8 exactly equals 16.2+9.7. If there is a tiny, tiny difference, RANK may rank them according to this error. You are right to round before using RANK on floating point numbers.

 

[lionelhill]

Yes, I checked: 16.1+9.8 <> 16.2+9.7 in Excel world (and probably also not in any world where decimal is represented in binary; this is not a bug).
To see what's going on, calculate the two using the SUM function, and then add cells to calculate (Answer-25)*100, and format it with 20 decimal places. You will find that the two calculations have the results:

90.000000000000200000000000000000
89.999999999999900000000000000000