tag:blogger.com,1999:blog-1695460650467928609.post5437539894582467808..comments2024-04-22T02:08:46.681-07:00Comments on Software Testing Tricks: Multiplication Bug in Microsoft Excel 2007!Debasis Pradhanhttp://www.blogger.com/profile/15059356907987625705noreply@blogger.comBlogger11125tag:blogger.com,1999:blog-1695460650467928609.post-65933197447545126012009-09-09T10:55:13.684-07:002009-09-09T10:55:13.684-07:00Once I converted from xls to xlsx and something ha...Once I converted from xls to xlsx and something happened and data were lost.But I solved this issue-<a href="http://www.recoverytoolbox.com/how_to_repair_xls_files_of_office_2003.html" rel="nofollow">check xls repair</a>.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.Alexishttps://www.blogger.com/profile/07547255185381643744noreply@blogger.comtag:blogger.com,1999:blog-1695460650467928609.post-26205496612731636312007-10-12T01:52:00.000-07:002007-10-12T01:52:00.000-07:00Another strange thing happens when you try to make...Another 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.<BR/>The 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)<BR/>The error does not occur if the y-values is 10 times bigger or smaller...<BR/><BR/>Yours _*_Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1695460650467928609.post-56818752177019045582007-10-10T10:11:00.000-07:002007-10-10T10:11:00.000-07:00I'm not into math at all, so 99% of this is over m...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? :)Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1695460650467928609.post-38062203863211581922007-09-28T05:31:00.000-07:002007-09-28T05:31:00.000-07:00I have found around 12000 such number pairs were t...I have found around 12000 such number pairs were this bug occurs. Here<BR/>is a link to my blog where I have posted a few thousand.<BR/>http://pranjan.blogspot.com/2007/09/i-found-12000-recurrence-of-excel...<BR/>[blogspot.com] Here is the ruby code to get a list of these<BR/>numbers(however seems my pattern is not completely correct as only<BR/>12000 of the 72000 of these number pairs are actually reproducing the<BR/>bug) http://pranjan.blogspot.com/2007/09/ruby-code-for-unearthing-vista.html<BR/>[blogspot.com]Piyushhttps://www.blogger.com/profile/03025228683278274034noreply@blogger.comtag:blogger.com,1999:blog-1695460650467928609.post-15702675529242331732007-09-27T01:05:00.000-07:002007-09-27T01:05:00.000-07:00@ Alan,Yes I have tested with A1+2 too! It gives t...@ Alan,<BR/><BR/>Yes 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)! <BR/><BR/>Thanks for sharing the link to Joel's post. He has got a nice analysis of this interesting bug!<BR/><BR/>-DebasisDebasis Pradhanhttps://www.blogger.com/profile/15059356907987625705noreply@blogger.comtag:blogger.com,1999:blog-1695460650467928609.post-17601598666785077432007-09-26T23:49:00.000-07:002007-09-26T23:49:00.000-07:00Expand your boundaries. Did you try A1 + 2? Perhap...Expand your boundaries. Did you try A1 + 2? Perhaps floating point numbers <I>around </I> 65535 have the error?<BR/><BR/>Joel explains the details better than I can. http://www.joelonsoftware.com/items/2007/09/26b.htmlAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-1695460650467928609.post-11127618019079842552007-09-26T07:40:00.000-07:002007-09-26T07:40:00.000-07:00Microsoft's Response - This is an issue in a funct...<B>Microsoft's Response</B> - <I>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.</I><BR/><BR/>Well, I don't think that is just a display error! If it was so, then take this scenario!<BR/><BR/>A1 =850*77.1 --> 100000<BR/>B1 =A1+1 --> 100001 (This contradicts the above clarification from Microsoft. Does not it?)<BR/>C1 =A1-1 --> 65534<BR/>D1 =B1-C1 --> 2<BR/><BR/>What do you guys think about it?<BR/>-DebasisDebasis Pradhanhttps://www.blogger.com/profile/15059356907987625705noreply@blogger.comtag:blogger.com,1999:blog-1695460650467928609.post-46192469799466626802007-09-26T07:10:00.000-07:002007-09-26T07:10:00.000-07:00More interesting facts about this bug: For n = 1 t...<I><B>More interesting facts about this bug:<BR/></B></I> <BR/>For <B>n = 1 to 1000</B>, there are <B>at least</B> <A HREF="http://channel9.msdn.com/ShowPost.aspx?PostID=344112#344112" REL="nofollow">137 cases</A> where the multiplication result of <B>n*(65535/n)</B> comes as "100,000" instead of "65,535"!Debasis Pradhanhttps://www.blogger.com/profile/15059356907987625705noreply@blogger.comtag:blogger.com,1999:blog-1695460650467928609.post-606667940738162352007-09-26T06:21:00.000-07:002007-09-26T06:21:00.000-07:00Your blog is nice. I think you should add your blo...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.deepanjalihttps://www.blogger.com/profile/02705644426953694822noreply@blogger.comtag:blogger.com,1999:blog-1695460650467928609.post-8934730975345697962007-09-26T01:12:00.000-07:002007-09-26T01:12:00.000-07:00@ Alan,Thanks for the correction! I admit using wr...@ Alan,<BR/><BR/>Thanks for the correction! I admit using wrong words keeps haunting me! Anyway, you can see I had used – <I>It *seems* that *any* formula that should evaluate to 65,535 will act strangely. </I> 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. <BR/><BR/><I> For one, your examples all use multiplication.</I><BR/>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*.<BR/><BR/>Still, to avoid confusion, let me rephrase my original sentence:<BR/><B><I>It seems that *some* formula that involves *multiplication* and evaluates to 65,535 will act strangely.</I></B><BR/><BR/>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!<BR/><BR/>Thanks for your inputs Alan. I am glad that the first person who commented on this post was someone from Microsoft! :) Happy Testing...<BR/><BR/>-DebasisDebasis Pradhanhttps://www.blogger.com/profile/15059356907987625705noreply@blogger.comtag:blogger.com,1999:blog-1695460650467928609.post-8489534488523334262007-09-25T15:54:00.000-07:002007-09-25T15:54:00.000-07:00Careful with your words (or careful with your test...Careful with your words (or careful with your testing). You say that *any* formula that evaluates to 65535.<BR/><BR/>FOr 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.<BR/><BR/>I'll leave it to you to find the pattern.Anonymousnoreply@blogger.com