Help with ranking

[Stone]

Hi

Using "RANK" should automatically rank eg. 10 observations from 1 to 10 and
if 2 observations have the same value they should rank eg. 7 and 7 and the
next rank would then be 9.

I have made several collums and for som collums this is actually so. However
in som collums the ranking for two equel values is eg 7 and 8. Therefor the
same "rules" dos not aply for this collums.

Can somone explane this to me or even better come up with an solution.

PS: Sorry for my english (I am for Denmark)

 

[T. Valko]

The 2 values are probably not equal. There is probably a very small decimal
difference. If the values are decimals and are formatted to display in a
certain way they may *appear* to be equal but really are not.

For example:

10.025
10.031

If you *format* those values to *display* 2 decimal places:

10.03
10.03

They appear to be equal but the *displayed value* is not the true value. The
true values are 10.025 and 10.031 which are the values RANK is calculating.

 

[Stone]

Hi T. Valko

Thank you for your input.

Sorry but that's not possible. I put in the numbers:

Jan Feb Diff Rank
A 10,8 10,8 0,0 10
B 8,8 7,2 -1,6 15
C 11,9 11,4 -0,5 11 OK
D 7,8 5,6 -2,2 16
E 11,6 10,0 -1,6 14
F 7,5 7,0 -0,5 11 OK
G 8,2 9,6 1,4 5
H 8,5 11,2 2,7 1
I 13,0 13,6 0,6 8
J 6,0 7,8 1,8 3 !!
K 4,6 7,2 2,6 2
L 9,4 11,2 1,8 4 !!
M 10,6 11,5 0,9 7
N 13,7 15,0 1,3 6
O 9,3 9,5 0,2 9
P 11,1 9,8 -1,3 13


C and F is OK, but J and L should be rank 3 both.

 

[T. Valko]

It's a rounding issue but I lack the technical expertise to explain it so
that you would understand. The error is so small that it can't be detected
by formatting. For example:

7.8-6 = 1.8

So, if you enter this formula you would expect that the result should be
TRUE but it's not, the result is FALSE:

=(7.8-6)-1.8 = 0 = FALSE

(1.8) - 1.8 = -2.2E-16

-2.2E-16 = 0 = FALSE

In your posted sample data there are also duplicate -1.6 that have different
ranks:

B 8,8 7,2 -1,6 15
E 11,6 10,0 -1,6 14

If you round the diff column then you will get the ranks that you expect.

With your Jan and Feb data in the range A2:B17...

Use this as the diff formula entered in C2 and copied down to C17:

=ROUND(B2-A2,1)

See this article on rounding issues:

http://www.cpearson.com/Excel/rounding.htm

 

[Stone]

Hi Valko

YES YES YES

It realy helped. I do not understand why but it help.

As I said why:

7.80000 - 6.00000 = 1.80000
11.20000 - 9.40000 = 1.80000

is NOT the same I realy do not understand. However your solution was
briliant. So thank you very must here from Denmark.

But I still think that there is an eror in microsoft's Excel as these
figures are pretty accurate.

But anyway THANK you again

Stone

 

[T. Valko]

You're welcome. Thanks for the feedback!

This kind of rounding problem is posted here often. Others can explain it
much better than I can. It has to do with the computer converting binary
numbers to decimal numbers.

 

[David Biddulph]

You will understand the problem when you try to calculate the fixed point
binary exact representation of numbers like 7.8, 1.8, 11.2, and 9.4.
[Hint: there isn't one.]