Is there a bug in the RANK() function?

[FDibbins]

I was looking at a thread from an OP, and noticed something I cant explain.

In the table below, using just the RANK() function, most times, it gives duplicates the same rank, but sometimes it seems to give duplicate values consecutive ranks. the values appear to be the same, and even =EXACT() returns TRUE. Look at the entries in rows 16:17...same value, should both be rank 15, but they are not.

	N	O	P	Q
2	5.000	1	1
3	818.720	1	2
4	839.700	2	3
5	839.700	2	3
6	957.650	2	5
7	957.650	2	5
8	2488.560	1	7
9	3103.850	1	8
10	9124.720	1	9
11	10076.400	3	10
12	10076.400	3	10
13	10076.400	3	10
14	11520.000	1	13
15	14220.000	1	14
16	15114.600	2	15	TRUE
17	15114.600	2	16
18	26260.500	1	17
19	26690.400	1	18
20	30762.300	1	19

O2=COUNTIF($N$2:$N$20,N2)
P2=RANK(N2,$N$2:$N$20,1)
Q16=EXACT(N16,N17)

What am I missing here?

[FDibbins]

To add in to this...

If you include the =RANK(N2,$N$2:$N$20,1)+COUNTIF($N$2:N2,N2)-1 to handle teh tie-breaks, that then fails on those 2 rows

[joeu2004]

In the table below, using just the RANK() function, most times, it gives duplicates the same rank, but sometimes it seems to give duplicate values consecutive ranks. the values appear to be the same, and even =EXACT() returns TRUE. Look at the entries in rows 16:17...same value, should both be rank 15, but they are not.

The operative words are "appear to be the same".

Excel formats only up to the first 15 significant digits. For example, if A1 is =1/3 and A2 is 0.333333333333333, =EXACT(A1,A2) returns TRUE because they look the same when formatted to 15 significant digits. But =(A1-A2=0) returns FALSE because they truly are different binary representations.

FYI, A1 is 0.333333333333333,314829616256247390992939472198486328125, and A2 is 0.333333333333332,981762708868700428865849971771240234375. The comma demarcates the first 15 significant digits.

Likewise, I suspect that =(N16-N17=0) returns FALSE.

Caveat: But =(N16=N17) might return TRUE, just as =(A1=A2) returns TRUE in my example with 1/3. That is due to a dubious heuristic whereby Excel sometimes considers two values to be equal if they are "close enough". The operative word is "sometimes". Excel applies the heuristic inconsistently, as demonstrated by the difference between =(A1=A2) and =(A1-A2=0). In particular, Excel does not implement the heuristic in most functions.

[FDibbins]

Both the values in those 2 rows go to only 1 decimal place, I took them out to 3 places to try and show that.

=(N16-N17=0) does return FALSE, and when I type the 1st value in manually, the rank returns 15 for both. But this is not a 15-decimal value, it is a single dec-value

Still doesnt explain it, but thanks for the input

[joeu2004]

Both the values in those 2 rows go to only 1 decimal place, I took them out to 3 places to try and show that.

=(N16-N17=0) does return FALSE, and when I type the 1st value in manually, the rank returns 15 for both. But this is not a 15-decimal value, it is a single dec-value

Still doesnt explain it

Yes it does, by your own description. As you said, =(N16-N17=0) is FALSE. That proves they are different in your Excel file.

The devil is in the details. Attach an example Excel file.

The fact that the numbers appear to be the same when formatted to 3 decimal places -- even to 10 decimal places (15 significant digits) -- is not sufficient to say "this is not a 15-decimal value".

First, most non-integers are "15-decimal values". For example, the constant 15114.6 is represented by a binary representation whose exact decimal value is really 15114.6000000000,00363797880709171295166015625.

Second and more to the point, calculated values can appear to be the same up to 15 or fewer significant digits, but their binary representations can be different from each other or from similarly-looking constants, as I demonstrated with 1/3 and 0.3333333333333333.

I'm sure that in the original Excel file, N16 and/or N17 are calculated or entered differently.

For example, if N16 is =151.146*100 and N17 is =1511.46*10, both appear to be 15114.6 and even 15114.6000000000. Moreover, =(N16=N17) returns TRUE. But =(N16-N17=0) returns FALSE, and =MATCH(N16,N17,0) returns #N/A, demonstrating that their binary representations are different.

In fact, N16 is 15114.5999999999,985448084771633148193359375, and N17 is 15114.6000000000,00363797880709171295166015625.

[FDibbins]

no formulas, only 1 decimal place.

Also, no need to "reply with quotes" it just takes up unecessary space

[joeu2004]

The values in N16 and N17 are indeed different. N16 is 15114.5999999999,985448084771633148193359375, and N17 is 15114.6000000000,00363797880709171295166015625.

They were probably created by copy-and-paste-value or by a process with a similar effect (e.g. a macro). The value 15114.6 (approximately) was entered differently into the different cells that N16 and N17 originated from.

In any case, I'm just repeating myself. The fact that =(N16-N17=0) is FALSE demonstrates that N16 and N17 are different. The fact that they are different explains why RANK returns different values. No bug. That answers your original question.

I can only speculate about how they became different. If you are the originator of the file, you can tell us. But if you got the file from someone else, only he or she can tell us.

I'm done here.

[quekbc]

So, when I manually entered the numbers from the table you provided in the first post, I have no issues with the Rank. 16 and 17 shows the same rank.

However, in the attached file, it shows the issue. Copying it unto a new workbook yields the exact same results. This seems odd so I looked into the XML file and noticed this.

Please Login or Register  to view this content.

The numbers, whilst looking like a number to 1 dp, is actually more than that. What causes this? I don't know. How to prevent this? I am not sure either.

[FDibbins]

I looked into the XML file and noticed this.

Than accounts for why - like I said in post #4 - "I type the 1st value in manually, the rank returns 15 for both"

[quekbc]

Yeah. If only we get a dollar for every Excel annoying and random quirks we find.

[FDibbins]

Thank you both for looking into this for me

[joeu2004]

I looked into the XML file and noticed this.

Please Login or Register  to view this content.

The numbers, whilst looking like a number to 1 dp, is actually more than that. What causes this? I don't know. How to prevent this?

How to prevent this?
If you created the XML file from an Excel file (i.e. "xlsx", "xlsm" or "xls"), you can prevent this by ensuring that calculated values in the original Excel file are rounded to the degree of accuracy that you expect.

For example, instead of =151.146*100, write =ROUND(151.146*100,1) if you want accuracy to only 1 decimal place. Formatting alone only affects the appearance, not the underlying value.

If you did not create the XML file, you must address the problem after opening or importing the file. I'm afraid I know too little about XML files to be more specific.

What causes this?
It is a common misconception that standard 64-bit binary floating-point values can be represented by 15 significant decimal digits. In fact, the IEEE standard states that it takes 17 significant decimal digits to reproduce the binary representation.

And that is what we see in the XML file. 15114.599999999999 is one 17-digit representation of the binary value resulting from =151.146*100, for example.

That explains how the XML file came to look like that: it simply goes beyond the arbitrary limitations of Excel when the file is written.

If Excel interpreted XML files the same way that it does data entry and text files (incl. CSV files), the 17-digit constant 15114.599999999999 would be truncated (not rounded) to the 15-digit constant 15114.5999999999.

But since the latter is very different from 15114.6000000000, we know that is not how Excel interprets XML files.

Instead, Excel actually interprets the full 17-digit constant in XML files, just as VBA does. We can accomplish the same thing by writing the following in Excel: =15114.5999999999+"0.99E-10".

That explains how the differences in the XML file become differences in Excel. Excel simply goes beyond its usual arbitary limitations when interpreting an XML file.

As you noted, copying the constants usually preserves the unseen differences in their binary representations.

PS: There are 55 binary representations that Excel displays at 15114.6000000000 due to its arbitrary limitation of formatting up to 15 significant digits.

[FDibbins]

This was actually in response from this thread...
http://www.excelforum.com/excel-form...h-countif.html

[micope21]

Hi

Yes I had this problem in the past with decimal?

I change different formula using Sumproduct and round.

Rank

Please Login or Register  to view this content.

Copy down.

Both will be on 15.

Rank + Countif

Please Login or Register  to view this content.

Copy down.

Both will be on 15 and 16.

[micope21]

See the files