If this is your first visit, be sure to check out the Board FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.

Click here to reset your password. You MUST have a valid e-mail address so that you may receive the instructions to complete the reset.

Re: Ranking Issue

I ran Mohamed's sheet past the Excel MVP group and it is a very intriguing situation.

It is generally well known that Excel only had 15 digits of precision. That is why you find so many people complaining that they can not store their 16-digit credit card numbers in Excel without losing the last digit.

Some of my friends cracked open the XML of Mohamed's worksheet and discovered that Excel is actually storing seventeen digits . It turns out that Mohamed has four cells that appear in Excel as 115%.
But in the XML, those four cells are actually:
0.11499999999999999
0.11499999999999999
0.11499999999999999
0.11500000000000000

The maddening thing here is that Excel won't show more than 15 digits, so we can never see in the Excel user interface nor in VBA that the cell contains anything other than 115%.

COUNTIFS and =A2=A3 all treat all four of those cells as if they contain 115%.

But we can tell from how the RANK function and the Sort feature work that Excel is using the 16th and 17th digits. This causes problems.

Mohamed - your workaround is that when you calculate the percentage, you need to wrap that calculation in a rounding function to keep only 14 or 15 digits. =ROUND(A2/B2,15) will prevent this from happening again.

I am guessing this won't make you happy. But the fact is that it will solve your problem. The other solution would be to push Microsoft to make RANK and sorting less accurate, but that will slow Excel calculation down for 749,999,999 other people, and I am not going to advocate for less accuracy and slower Excel.

Re: Ranking Issue

Originally Posted by MrExcel

I ran Mohamed's sheet past the Excel MVP group and it is a very intriguing situation.

It is generally well known that Excel only had 15 digits of precision. That is why you find so many people complaining that they can not store their 16-digit credit card numbers in Excel without losing the last digit.

Some of my friends cracked open the XML of Mohamed's worksheet and discovered that Excel is actually storing seventeen digits . It turns out that Mohamed has four cells that appear in Excel as 115%.
But in the XML, those four cells are actually:
0.11499999999999999
0.11499999999999999
0.11499999999999999
0.11500000000000000

The maddening thing here is that Excel won't show more than 15 digits, so we can never see in the Excel user interface nor in VBA that the cell contains anything other than 115%.

COUNTIFS and =A2=A3 all treat all four of those cells as if they contain 115%.

But we can tell from how the RANK function and the Sort feature work that Excel is using the 16th and 17th digits. This causes problems.

Mohamed - your workaround is that when you calculate the percentage, you need to wrap that calculation in a rounding function to keep only 14 or 15 digits. =ROUND(A2/B2,15) will prevent this from happening again.

I am guessing this won't make you happy. But the fact is that it will solve your problem. The other solution would be to push Microsoft to make RANK and sorting less accurate, but that will slow Excel calculation down for 749,999,999 other people, and I am not going to advocate for less accuracy and slower Excel.

Thanks MR Excel For your caring on my case
I'll use round to get rank as a facade