2 equal values not working properly with rank function

[KyleX]

I have 2 numbers, both 18.19 in value, but the rank function treats them differently. Usually the rank function will assign the same rank to numbers of the same value, but here it assigns a different value, which is wrecking my otherwise nicely working VBA script. Out of thousands of trials I've only seen this once, in this instance. Here is the data:

	B	C	D	E	F	G	H	I	J
Data	Rank	Data	Rank
=B4=B9	=G4=G9

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]18.26[/TD]
[TD="align: right"] 2.00 [/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]18.26[/TD]
[TD="align: right"] 2.00 [/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]18.19[/TD]
[TD="align: right"] 3.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"] TRUE [/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]18.19[/TD]
[TD="align: right"] 3.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]11.65[/TD]
[TD="align: right"] 6.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]11.65[/TD]
[TD="align: right"] 6.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]15.18[/TD]
[TD="align: right"] 5.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]15.18[/TD]
[TD="align: right"] 5.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]18.19[/TD]
[TD="align: right"] 4.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]18.19[/TD]
[TD="align: right"] 3.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]24.42[/TD]
[TD="align: right"] 1.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]24.42[/TD]
[TD="align: right"] 1.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E4[/TH]
[TD="align: left"]=B4=B7[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J4[/TH]
[TD="align: left"]=G4=G7[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C3[/TH]
[TD="align: left"]=RANK(B3,$B$3:$B$8,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C4[/TH]
[TD="align: left"]=RANK(B4,$B$3:$B$8,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C5[/TH]
[TD="align: left"]=RANK(B5,$B$3:$B$8,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C6[/TH]
[TD="align: left"]=RANK(B6,$B$3:$B$8,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C7[/TH]
[TD="align: left"]=RANK(B7,$B$3:$B$8,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C8[/TH]
[TD="align: left"]=RANK(B8,$B$3:$B$8,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H3[/TH]
[TD="align: left"]=RANK(G3,$G$3:$G$8,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H4[/TH]
[TD="align: left"]=RANK(G4,$G$3:$G$8,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H5[/TH]
[TD="align: left"]=RANK(G5,$G$3:$G$8,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H6[/TH]
[TD="align: left"]=RANK(G6,$G$3:$G$8,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H7[/TH]
[TD="align: left"]=RANK(G7,$G$3:$G$8,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H8[/TH]
[TD="align: left"]=RANK(G8,$G$3:$G$8,0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



The second 2 columns are the first columns copy and pasted. Then, all I did was copy the first instance of 18.19 and paste it into the second instance. Now the rank function treats them properly as equals. Why were they not equal before?? I'm so lost I'd really appreciate any insight, even guesses. Thanks!

 

[dreid1011]

This sounds like the known floating-point issue with the way Excel calculates.

https://support.microsoft.com/en-us...-rounding-errors-in-floating-point-arithmetic

It may help you out if you specifically rounded your values to 2 decimal places.

 

[AhoyNC]

Are the numbers coming from a formula?
Try expanding the number of decimal points and you may find the numbers are different.

 

[KyleX]

I checked that and it doesn't seem like there is any noticeable difference in the values. I can bring it out to 20 decimal points, they're all 0s after the first 2, for both values. The values don't even come from formulas!
Thanks for your reply though!

Book1
	B	C
2	Data	Rank
3	18.26	2.00
4	18.1900000000000000000000000000000000000000000000000	3.00
5	11.65	6.00
6	15.18	5.00
7	18.1900000000000000000000000000000000000000000000000	4.00
8	24.42	1.00
Sheet1

Cell Formulas
Range	Formula
C3	=RANK(B3,$B$3:$B$8,0)
C4	=RANK(B4,$B$3:$B$8,0)
C5	=RANK(B5,$B$3:$B$8,0)
C6	=RANK(B6,$B$3:$B$8,0)
C7	=RANK(B7,$B$3:$B$8,0)
C8	=RANK(B8,$B$3:$B$8,0)

 

[KyleX]

This sounds like the known floating-point issue with the way Excel calculates.

https://support.microsoft.com/en-us...-rounding-errors-in-floating-point-arithmetic

It may help you out if you specifically rounded your values to 2 decimal places.

Thank you for this. So even though I can bring the decimal places out to as far as I can see, and everything matches, the values still might different in the "background"?

Odd that the countif function treats the values the same, but the rank function treats them differently. Both the methods in the link you provided worked, although I'm a bit wary to use precision as displayed, so I'll just incorporate rounding into my VBA code.

 

[dreid1011]

I'm glad that helped you out. I usually try to round my numbers off to the most significant decimal place I might need, just to avoid any potential issues like this.