Software

Tip: Excel 2007 has interesting bug


Thanks to John Bartow for sending a link to Excel Can't Multiply.  I tried it, and it's true. Be sure to read the first response because he share some interesting insight.

I can't add anything to the article. I found that adding 1 to the result, =850*77.1 +1, returns 100,001. However, adding a value via a dependent reference, =cell + 1 forced the right answer, at least with the few I tried. Also, adding a value other than 1 forced the right answer in the full expression: =850*77.1 + 2

I didn't play anymore with it much. I was curious how deep the error might ripple through dependent expressions.  

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

6 comments
farrell_av3
farrell_av3

My excel column letters have changed to numbers how do I change it back to letters.

dion1030
dion1030

Not only does the EXCEL error on many other numbers for multiplication, the conditional formatting behaves as if the correct number is being displayed as well. It seems likely that when they fix the the bug, this will remedy itself... hopefully.

SWThomas
SWThomas

The problem has been extensively discussed in the blogosphere. It turns out it is a display problem, not an arithmetic problem. It is detailed here: blogs.msdn.com/excel/archive/2007/09/25/calculation-issue-update.aspx Here's the nub of it: Of the 9.214*10^18 different floating point numbers that Excel 2007 can store, there are 6 floating point numbers (using binary representation) between 65534.99999999995 and 65535, and 6 between 65535.99999999995 and 65536 that cause this problem. You can?t actually enter these numbers into Excel directly (since Excel will round to 15 digits on entry), but any calculation returning one of those results will display this issue if the results of the calculation are displayed in a cell. They emphasize that this is *only* (:-?) a display problem. The number is calculated and stored correctly, but displayed incorrectly.

MJGunther
MJGunther

Office Watch has documented this problem (and that it is *more* than a mere display problem) extensively. See the link below for demonstration and discussion. EXCEL'S PROBLEM WITH 65,535 & 65,536 See this article online with sample worksheet at http://news.office-watch.com?556

ssharkins
ssharkins

I read that discussion soon after reading the article I linked. I've seen a number of discussions and the idea that it's "only" a display problem is odd -- if I sent such a number to a check run, it certainly would matter.

Editor's Picks