Rank Error

[Tom Kerr]

I am getting unexpected results when using the RANK function on a column of
calculated results. A simple example demonstrates the unexpected behavior.

A B C D
29 27.2 1.8 1
39 37.2 1.8 3
29 27.2 1.8 1

The formulas are:
column C: =$A1-$B1
column D: =RANK($C1,$C$1:$C$3,0)

Any idea why this is not ranked as a 3-way tie (i.e 1,1,1)?

 

[Max]

Perhaps due to the underlying values in cols A and B ..

Try instead in C1: =ROUND($A1-$B1,1)
Copy down

This should "correct" the ranking in col D

 

[Tom Kerr]

That worked great - Thanks!

Perhaps due to the underlying values in cols A and B ..

Try instead in C1: =ROUND($A1-$B1,1)
Copy down

This should "correct" the ranking in col D

 

[Max]

You're welcome !
Thanks for posting back ..

 

[Jerry W. Lewis]

Max gave the simplest workaround. As for the reason, remember that
computers do math in binary. Most exact decimal fractions (including
..2) have no exact binary representation. Thus both 27.2 and 37.2 must
be approximated. The subsequent math is exactly correct based on the
approximate inputs.

Calculate =C1-1.75, copy down, and format to show 17 decimal places.
You will see that your supposedly equal values of 1.8 differ from each
other in the 15th decimal place due to the binary approximations. You
cannot see this difference by directly formatting the 1.8 values since
Excel will not display more than 15 significant figures (see Help on
"Excel specifications and limits" subtopic "Calculation specifications").

Jerry