Update: Please refer to the "Comments" section of this post for more interesting facts/analysis on this bug!I came across this interesting error while surfing through Google groups! On 23rd Sept 2007, this error was first reported by one of the group members.
Bug Characteristics:
1. When you try to multiply 850 by 77.1 excel displays the result to be 100,000 instead of the correct answer, i.e. 65,535!
2. It seems that *some* formula that involves *multiplication* and evaluates to 65,535 will act strangely. [
3. If you add 1 to a cell that has one of these incorrect results you get 100001, but deduct 1, and you get 65,534!
4. Displays correct result in Microsoft Excel 2000 and 2003. So it appears to be an issue infesting Excel 2007 alone!
5. Even SUMPRODUCT returns 100000!
=SUMPRODUCT(850,77.1)
=SUMPRODUCT(850,77.1,2,0.5)
Let me Analyze the Error:
Did you notice the peculiarity about the error? This happens for some multiplication formula that should evaluate to 65,535! Like,
=5.1*12850
=10.2*6425
Let me Analyze the Error:
Did you notice the peculiarity about the error? This happens for some multiplication formula that should evaluate to 65,535! Like,
=5.1*12850
=10.2*6425
=20.4*3212.5
=40.8*1606.25
=77.1*850
=154.2*425
=212.5*308.4
=308.4*212.5
=425*154.2 and so on… Here is a screenshot depicting the weird calculation!
So it appears to me that it the specialty of the number 65,535 that makes the error unique! Interestingly enough, 65,535 is the highest number which can be represented by an unsigned 16 bit binary and 65,536 requires 32 bits. In other words, the number, 65,535, happens to have two bytes worth of 1 digits in binary. I understand, Microsoft Excel 2007 might be using floating point for numbers, and hence it might not apply here, still I doubt this has got a lot to do with this error! Bugs are usually encountered at boundaries. I wonder how the test team at Microsoft, who were testing the multiplication feature, could miss out such an obvious looking test! Also I am interested to see how Microsoft is going to react to this bug!
Can you see some more interesting attributes of this error? Can you link this to some other testing perspective? Do let me know your viewpoints on this by leaving your comments.
Happy Testing…
So it appears to me that it the specialty of the number 65,535 that makes the error unique! Interestingly enough, 65,535 is the highest number which can be represented by an unsigned 16 bit binary and 65,536 requires 32 bits. In other words, the number, 65,535, happens to have two bytes worth of 1 digits in binary. I understand, Microsoft Excel 2007 might be using floating point for numbers, and hence it might not apply here, still I doubt this has got a lot to do with this error! Bugs are usually encountered at boundaries. I wonder how the test team at Microsoft, who were testing the multiplication feature, could miss out such an obvious looking test! Also I am interested to see how Microsoft is going to react to this bug!
Can you see some more interesting attributes of this error? Can you link this to some other testing perspective? Do let me know your viewpoints on this by leaving your comments.
Happy Testing…
Careful with your words (or careful with your testing). You say that *any* formula that evaluates to 65535.
ReplyDeleteFOr one, your examples all use multiplication. The more important fact that most testers would discover while trying to narrow down the cause would be to note that 212.5 * 308.4 and 850*77.1 demonstrate the bug and that 21.25 * 3084 or 2125 * 30.84 or 8.5*7710 do not.
I'll leave it to you to find the pattern.
@ Alan,
ReplyDeleteThanks for the correction! I admit using wrong words keeps haunting me! Anyway, you can see I had used – It *seems* that *any* formula that should evaluate to 65,535 will act strangely. I thought using *seems* should negate the effect of *any*. However, I understand that I should not have used the word *any* to avoid confusion.
For one, your examples all use multiplication.
As far as *multiplication* is concerned I am ONLY talking about a bug that results due to multiplication (you may verify this from the title of the post!). So, in turn this excludes formulae that do not explicitly involve *multiplication*.
Still, to avoid confusion, let me rephrase my original sentence:
It seems that *some* formula that involves *multiplication* and evaluates to 65,535 will act strangely.
Furthermore, 212.5 * 308.4 and 850*77.1 demonstrate the bug while 21.25 * 3084 or 8.5*7710 does not. That suggests that the series that demonstrates the bug are related by factors of 2 (binary!) and are NOT related by factors of 10(decimal!). That is probably the most interesting part! This further strengthens my suspicion that this bug has something to do with the 16 bits and 32 bits boundary!
Thanks for your inputs Alan. I am glad that the first person who commented on this post was someone from Microsoft! :) Happy Testing...
-Debasis
Your blog is nice. I think you should add your blog at www.blogadda.com and let more people discover your blog. It's a great place for Indian bloggers to be in and I am sure it would do wonders for your blog.
ReplyDeleteMore interesting facts about this bug:
ReplyDeleteFor n = 1 to 1000, there are at least 137 cases where the multiplication result of n*(65535/n) comes as "100,000" instead of "65,535"!
Microsoft's Response - This is an issue in a function that puts numbers in cells, so the values in Excel's memory are actually correct. Imagine A1 contains =77.1*850 ... Excel actually calculates the correct answer, and you can see that if you use VBA to check the value for A1 - it will be 65535. But in the function that takes that value and formats it to be displayed on the screen, for the values described above, there is a bug. Any calculations based off that cell will be accurate too.
ReplyDeleteWell, I don't think that is just a display error! If it was so, then take this scenario!
A1 =850*77.1 --> 100000
B1 =A1+1 --> 100001 (This contradicts the above clarification from Microsoft. Does not it?)
C1 =A1-1 --> 65534
D1 =B1-C1 --> 2
What do you guys think about it?
-Debasis
Expand your boundaries. Did you try A1 + 2? Perhaps floating point numbers around 65535 have the error?
ReplyDeleteJoel explains the details better than I can. http://www.joelonsoftware.com/items/2007/09/26b.html
@ Alan,
ReplyDeleteYes I have tested with A1+2 too! It gives the result as if A1 was 65,535! And so does A1-1 and B1-C1 (please refer my above comment). Perhaps you are right. The problem occurs with floating point numbers around 65,535 and in some cases around 65,536 (A1+1)!
Thanks for sharing the link to Joel's post. He has got a nice analysis of this interesting bug!
-Debasis
I have found around 12000 such number pairs were this bug occurs. Here
ReplyDeleteis a link to my blog where I have posted a few thousand.
http://pranjan.blogspot.com/2007/09/i-found-12000-recurrence-of-excel...
[blogspot.com] Here is the ruby code to get a list of these
numbers(however seems my pattern is not completely correct as only
12000 of the 72000 of these number pairs are actually reproducing the
bug) http://pranjan.blogspot.com/2007/09/ruby-code-for-unearthing-vista.html
[blogspot.com]
I'm not into math at all, so 99% of this is over my head, but I did find it rather coincidental that the maximum number of rows in Excel 2003 is 65,536. Coincidence or Conspiracy? :)
ReplyDeleteAnother strange thing happens when you try to make a regression between x-values {10, 20, 30, 40, 50} and y-values {100000, 99990, 99980, 99970, 99960, 99950} using a xy-point diagram.
ReplyDeleteThe intersection with the vertical axix is a factor 10 wrong, if you right-click a point and ask for a "tendency-line" (I don't know what the english version call a regression line)
The error does not occur if the y-values is 10 times bigger or smaller...
Yours _*_
Once I converted from xls to xlsx and something happened and data were lost.But I solved this issue-check xls repair.This tool did it very quickly.Moreover tool is free and can too helps to prevent a broad range of issues that lead to the corruption of Microsoft Excel worksheets.
ReplyDelete