Breaking news on a bad recalc bug in Excel. If you ever use RANK or Sort, you could be impacted.
Fundamental belief: Excel stores 15 digits of precision.
How RANK returns duplicates
How RANK+COUNTIF eliminates duplicates
Mystery where RANK+COUNTIF still returns a duplicate
Four cells contain 1.15
=A2=A3 shows the cells are equal
Ctrl+` shows the cells are equal
But the four cells do not sort correctly. Something is different.
Change the XLSX to a Zip file
Open the XML inside the Zip File
What? Excel is storing 17 digits!?!
So - is that a good thing? More accurate, right? Maybe 16-digit credit card numbers stored as numbers, right? Hooray?
But... while some functions only use 15 digits (COUNTIF and equality tests) others use 17 digits (RANK and sorting). One formula that uses a mix of RANK+COUNTIF will potentially return the wrong answer. What happened to "Recalc or Die"?
One workaround, wrap your values to be ranked in =ROUND(A2,14).
Fundamental belief: Excel stores 15 digits of precision.
How RANK returns duplicates
How RANK+COUNTIF eliminates duplicates
Mystery where RANK+COUNTIF still returns a duplicate
Four cells contain 1.15
=A2=A3 shows the cells are equal
Ctrl+` shows the cells are equal
But the four cells do not sort correctly. Something is different.
Change the XLSX to a Zip file
Open the XML inside the Zip File
What? Excel is storing 17 digits!?!
So - is that a good thing? More accurate, right? Maybe 16-digit credit card numbers stored as numbers, right? Hooray?
But... while some functions only use 15 digits (COUNTIF and equality tests) others use 17 digits (RANK and sorting). One formula that uses a mix of RANK+COUNTIF will potentially return the wrong answer. What happened to "Recalc or Die"?
One workaround, wrap your values to be ranked in =ROUND(A2,14).
Transcript of the video:
Learn Excel from MrExcel podcast, episode 2110.
15 digits of precision.
Maybe not always.
Hey, welcome back to the MrExcel net cast.
I am Bill Jelen.
And now this is for the people who have this fundamental belief about Excel, that numbers and Excel only have 15 digits of precision, and you get burned by this.
You know this because if you try and pay some credit card numbers, so these credit card numbers from no Patty or the fake credit card numbers, of course, don't go try and steal the credit card numbers here ending in one, two, three, four, five, six, seven, eight, nine.
When it pays those to Excel.
First, I get scientific notation, but I change it back and Excel can't deal with the 16 digits.
It can only deal with 15 digits is a well-known.
It's a thing.
All right.
Here's another thing that's well known is if you have 10 people here and you have their scores and you use the rank function equal rank of before within that range, and two people are tied.
Excel is going to rank both of those people, Edie and Helen, as a rank of three and here Della, and, and even with a rank of six and no one is going to be ranked four.
All right, this is, this is well-known well-documented.
And the reason that this irritates me is because I like to use this trick where I start with a formula using index and match, and whoever was supposed to be ranked four is instead ranked three.
So Helen doesn't show up in this list and whoever's supposed to be ranked seven is ranked six.
So even it doesn't show up in the list, but that's okay.
We have a great workaround for this.
The workaround is we take the rank and then we add to it, how many things above us, how many things above us are equal to this item.
All right.
So let's come down here to Ivina.
All right.
So Ivina,uthe rank is going to return six, but then we say, well, look at everything above us from B3 to be 11 B3, to be 11 that's this right here and see how many times 40 appears in that list.
Oh, well it appears once in that list.
So we're going to arbitrarily give Ivina rank of seven.
Is it fair that she gets a seven instead of a six?
No, but it allows my sort with a formula to work correctly.
All right.
Well-known work around.
It happens a lot.
But then last week on YouTube, I get a question saying, Hey, can you go check this question?
The mr.
Excel message board.
And I went out and looked and Muhammad's using a variation of this, a ranking count.
And he says, ah, it's not working.
Right.
And the people that mr.
Excel message force, Oh no, you're just not doing it.
Right.
But he posted the spreadsheet.
He said we were downloaded look.
And he was doing it right.
Umhere were, halues here in, for some reason to people using this formula that works is supposed to work.
Two people are being ranked seven and no one's being ranked four.
I have to tell you it was a mystery.
And you know, I tried, I tried all the things.
I tried, all the things.
Here's the first thing I tried.
I said, well, let's, let's see if what's stored behind the scenes is really equal.
Is this 115% equal to that?
150% Excel said, yes.
Is this 115% equal to that?
115% sure.
How about this one?
Is this one equal to that one?
Yeah.
How about this one?
Is this one equal to that one?
So according to Excel, these things all are storing 115% and you think, Oh, it's a floating point decimal problem, but let's do control in the Grove accents.
So we can see what's really being stored in there.
And sure enough, these, you know, these were calculations and some of them are being stored out to a lot of digits, but the 115 or the 1.15 it's, it's just, it's flat out 1.15.
You know, but then I said, well, well, let's try this, let's try this.
Let's see what's going on.
You know, it's a mr.
I can't figure it out.
So I came over here and I sorted descending based on the score.
Right?
And when you sort your setting and you have a Tai, Claire flow, even a and Lucy should all come together.
All right.
Now, watch this watch.
I'm going to do data Zita, a Claire flow.
Ivina they stay in the same order.
But for some reason, Lucy, Lucy jumped from last to first.
That's shouldn't happen.
If this was a dead tie, there's no reason that Lucy should move in.
In the case of a tie, it stays in the original order.
There should be clear flow.
Ivina a Lucy, not Lucy, Claire flow, Ivina there's something going on here.
All right.
So I asked a few people and someone said, well, Hey, let's crack this thing.
Open, change her from a dot XLSX to a.zip dig into the XML.
And they pointed out in the X summit.
L those four values are not 1.15 it's 1.4 and 900, a whole bunch of nights, 1.499 1.501.
Right?
And we pulled this number out and I put it here in a corner, new font.
So it would line up.
And then here, I just counted how many digits there are.
And this is me just typing one space, two, three, four, five, six, seven, eight, nine, ten one, two that's 17 digits.
I mean, I, I checked it.
I looked, did I screw something up?
Did I miss some digits?
No.
Excel is storing 17 digits.
Hey, what's up with this Excel only stores, 15 digits.
Everyone knows accelerating stores, 15 digits, but for some reason, and the XML and this file, they're storing 17 digits.
And the first thing I think of is, yes, that means that they're working on the credit card problem.
That means they're working on handling 16 digits.
This'll be a great improvement, but here's the problem.
So right now, even though they're storing 17 data, it's a lot of Excel functions.
Count.
If, COUNT IFS the equality test is this equal to that are using 15 digits.
But some things like sorting and rank are breaking the rules and using all 17 digits.
All right.
And so that means that this well-known work around for rank plus County.
If, if you happen to have something that's different in the 16th or 17th, digit is going to return the wrong answer, that's bad.
We don't like Excel to return the wrong answer.
All right.
I don't know when they started storing 17 digits, it's just kind of dumb luck that we ran across this file and realized that they're storing 17 digits.
So that means that we have this, well, let's say it's a fairly bad work around.
So we're going to have to do is whatever calculation you were doing to get these numbers, whatever calculation that you're going to have to start to wrap that calculation in an equal round.
Round, you're going to have to take that number and round it to the nearest 15, 14 something.
Right?
So that we even, even in the case where they're secretly storing 17 digits, you're going to force it to be in this case 14 after the decimal one, before the decimal 15 digits.
And now that fixes our problem, everyone has every number, every rank appears exactly.
Exactly.
Once.
Yeah.
This is a weird one.
This is, you know, fundamental belief, 15 digits of precision.
It's been that way for a long, long time.
And it's like, they're, they're halfway to fixing it.
Maybe going to 17.
Some of the functions are using 17 and some are using 15.
He'd say, well, why does that matter?
It's out there in the 16th and 17th position, but in this case, it clearly matters.
And it, it, it, you know, it shakes your faith in in recalc or die.
Right?
Is it something, something is wrong here.
Hey, I wanna thank you for stopping by.
I'll see you next time for another net cast from MrExcel.
15 digits of precision.
Maybe not always.
Hey, welcome back to the MrExcel net cast.
I am Bill Jelen.
And now this is for the people who have this fundamental belief about Excel, that numbers and Excel only have 15 digits of precision, and you get burned by this.
You know this because if you try and pay some credit card numbers, so these credit card numbers from no Patty or the fake credit card numbers, of course, don't go try and steal the credit card numbers here ending in one, two, three, four, five, six, seven, eight, nine.
When it pays those to Excel.
First, I get scientific notation, but I change it back and Excel can't deal with the 16 digits.
It can only deal with 15 digits is a well-known.
It's a thing.
All right.
Here's another thing that's well known is if you have 10 people here and you have their scores and you use the rank function equal rank of before within that range, and two people are tied.
Excel is going to rank both of those people, Edie and Helen, as a rank of three and here Della, and, and even with a rank of six and no one is going to be ranked four.
All right, this is, this is well-known well-documented.
And the reason that this irritates me is because I like to use this trick where I start with a formula using index and match, and whoever was supposed to be ranked four is instead ranked three.
So Helen doesn't show up in this list and whoever's supposed to be ranked seven is ranked six.
So even it doesn't show up in the list, but that's okay.
We have a great workaround for this.
The workaround is we take the rank and then we add to it, how many things above us, how many things above us are equal to this item.
All right.
So let's come down here to Ivina.
All right.
So Ivina,uthe rank is going to return six, but then we say, well, look at everything above us from B3 to be 11 B3, to be 11 that's this right here and see how many times 40 appears in that list.
Oh, well it appears once in that list.
So we're going to arbitrarily give Ivina rank of seven.
Is it fair that she gets a seven instead of a six?
No, but it allows my sort with a formula to work correctly.
All right.
Well-known work around.
It happens a lot.
But then last week on YouTube, I get a question saying, Hey, can you go check this question?
The mr.
Excel message board.
And I went out and looked and Muhammad's using a variation of this, a ranking count.
And he says, ah, it's not working.
Right.
And the people that mr.
Excel message force, Oh no, you're just not doing it.
Right.
But he posted the spreadsheet.
He said we were downloaded look.
And he was doing it right.
Umhere were, halues here in, for some reason to people using this formula that works is supposed to work.
Two people are being ranked seven and no one's being ranked four.
I have to tell you it was a mystery.
And you know, I tried, I tried all the things.
I tried, all the things.
Here's the first thing I tried.
I said, well, let's, let's see if what's stored behind the scenes is really equal.
Is this 115% equal to that?
150% Excel said, yes.
Is this 115% equal to that?
115% sure.
How about this one?
Is this one equal to that one?
Yeah.
How about this one?
Is this one equal to that one?
So according to Excel, these things all are storing 115% and you think, Oh, it's a floating point decimal problem, but let's do control in the Grove accents.
So we can see what's really being stored in there.
And sure enough, these, you know, these were calculations and some of them are being stored out to a lot of digits, but the 115 or the 1.15 it's, it's just, it's flat out 1.15.
You know, but then I said, well, well, let's try this, let's try this.
Let's see what's going on.
You know, it's a mr.
I can't figure it out.
So I came over here and I sorted descending based on the score.
Right?
And when you sort your setting and you have a Tai, Claire flow, even a and Lucy should all come together.
All right.
Now, watch this watch.
I'm going to do data Zita, a Claire flow.
Ivina they stay in the same order.
But for some reason, Lucy, Lucy jumped from last to first.
That's shouldn't happen.
If this was a dead tie, there's no reason that Lucy should move in.
In the case of a tie, it stays in the original order.
There should be clear flow.
Ivina a Lucy, not Lucy, Claire flow, Ivina there's something going on here.
All right.
So I asked a few people and someone said, well, Hey, let's crack this thing.
Open, change her from a dot XLSX to a.zip dig into the XML.
And they pointed out in the X summit.
L those four values are not 1.15 it's 1.4 and 900, a whole bunch of nights, 1.499 1.501.
Right?
And we pulled this number out and I put it here in a corner, new font.
So it would line up.
And then here, I just counted how many digits there are.
And this is me just typing one space, two, three, four, five, six, seven, eight, nine, ten one, two that's 17 digits.
I mean, I, I checked it.
I looked, did I screw something up?
Did I miss some digits?
No.
Excel is storing 17 digits.
Hey, what's up with this Excel only stores, 15 digits.
Everyone knows accelerating stores, 15 digits, but for some reason, and the XML and this file, they're storing 17 digits.
And the first thing I think of is, yes, that means that they're working on the credit card problem.
That means they're working on handling 16 digits.
This'll be a great improvement, but here's the problem.
So right now, even though they're storing 17 data, it's a lot of Excel functions.
Count.
If, COUNT IFS the equality test is this equal to that are using 15 digits.
But some things like sorting and rank are breaking the rules and using all 17 digits.
All right.
And so that means that this well-known work around for rank plus County.
If, if you happen to have something that's different in the 16th or 17th, digit is going to return the wrong answer, that's bad.
We don't like Excel to return the wrong answer.
All right.
I don't know when they started storing 17 digits, it's just kind of dumb luck that we ran across this file and realized that they're storing 17 digits.
So that means that we have this, well, let's say it's a fairly bad work around.
So we're going to have to do is whatever calculation you were doing to get these numbers, whatever calculation that you're going to have to start to wrap that calculation in an equal round.
Round, you're going to have to take that number and round it to the nearest 15, 14 something.
Right?
So that we even, even in the case where they're secretly storing 17 digits, you're going to force it to be in this case 14 after the decimal one, before the decimal 15 digits.
And now that fixes our problem, everyone has every number, every rank appears exactly.
Exactly.
Once.
Yeah.
This is a weird one.
This is, you know, fundamental belief, 15 digits of precision.
It's been that way for a long, long time.
And it's like, they're, they're halfway to fixing it.
Maybe going to 17.
Some of the functions are using 17 and some are using 15.
He'd say, well, why does that matter?
It's out there in the 16th and 17th position, but in this case, it clearly matters.
And it, it, it, you know, it shakes your faith in in recalc or die.
Right?
Is it something, something is wrong here.
Hey, I wanna thank you for stopping by.
I'll see you next time for another net cast from MrExcel.
