You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Tips
How-to
News
Videos
Stories
Yesterday we were alerted to an issue in Excel 2007 (and Excel Services 2007) involving calculation of numbers around 65,535. The Excel team would like to provide a description of the issue and explain what we're doing about it.
BackgroundYesterday evening we were alerted to an issue in Excel 2007 (and Excel Services 2007) involving calculation of numbers around 65,535. The first example that we heard about was =77.1*850, but it became clear from our testing as well as additional reports that this was just one instance where Excel 2007 would return a value of 100,000 instead of 65,535. The majority of these additional reports were focused on multiplication (ex. =5.1*12850; =10.2*6425; =20.4*3212.5 ), but our testing showed that this really didn't have anything do to with multiplication - it manifested itself with many but not all calculations in Excel that should have resulted in 65,535 (=65535*1 and =16383.75*4 worked for instance). Further testing showed a similar phenomenon with 65,536 as well. This issue only exists in Excel 2007, not previous versions.
The ProblemThis issue was introduced when we were making changes to the Excel calculation logic in the Office 2007 time frame. Specifically, Excel incorrectly displays the result of a calculation in 12 very specific cases (outlined below). The key here is that the issue is actually not in the calculation itself (the result of the calculation stored in Excel’s memory is correct), but only in the result that is shown in the sheet. Said another way, =850*77.1 will display an incorrect value, but if you then multiply the result by 2, you will get the correct answer (i.e. if A1 contains “=850*77.1”, and A2 contains “=A1*2”, A2 will return the correct answer of 131,070).
So what, specifically, are the values that cause this display problem? Of the 9.214*10^18 different floating point numbers (floating point on wikipedia) 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. All other calculation results are not affected.
The SolutionWe take calculation in Excel very seriously and we do everything we can in order to ensure that calculation is correct for all cases. We’ve come up with a fix for this issue and are in the final phases of a broad test pass in order to ensure that the fix works and doesn’t introduce any additional issues - especially any other calculation issues. This fix then needs to make its way through our official build lab and onto a download site - which we expect to happen very soon. We’ll add another post once that’s taken place with a link to the download.
Comments: (205) Collapse
The solution: throw away Excel and use OpenOffice Calc.
What simpleton would believe that Closed Source, proprietary software is better than Open Source.
I'll use the old, tired, example of purchasing an Automobile.
Would you purchase an Automobile with the hood (bonnet) welded shut. You could not fill it with gasoline unless you went to a special station. You were not allowed to perform your own maintenance on the automobile and further more a manual providing instructions on how to perform maintenance on the vehicle did not exist.
On the other hand, you can buy an automobile where you can open the hood. You can perform your own maintenance, there are thousands of individuals who can perform maintenance on this vehicle and you are able to interchange parts as you wish.
Open Source software, such as Open Office, passes MUCH MUCH more QA scrutiny than Microsoft Excel will ever see. At best, Microsoft has less than 100 QA testers working on Excel....which is precisely why such a bug got into the wild.
Open Office has EASILY 100 times this amount of QA scrutiny, precisely because ANYONE with any coding experience can act as a QA tester.
You don't see simple math errors such as 77.1*850 occurring in Open Office do you????
You've been drinking too much of the Microsoft Kool-Aid.
Oh, and by the way, both Google and IBM are backing Open Office
at excel prior to 2007 there are 256 Columns, which is the square root of 65536...!! Beware! Coincidence!! :-O
ok, joke aside.
@all the ignorant and lazy posters: You don't know about 65536 and computers? For you it's a similar stupid number like 34957? Fine, i have an advise:
a) get knowledge, f.i. by reading other posts where people share their wisdom
b) or just don't post and avoid to make your ignorance known to the public on forum related to *computer-matters*
@the rest: bad luck, it's just another mark on the microsoft joke list. However imo, what makes the problem less severe is that the error is so severe :-/
meaning: since 100K is "almost* the double of 65K the miscalc should be quite evident to a user (if you know your numbers and have some idea/feeling of what you want to get beforehand)
Good luck in fixing it, to the MS-guys!
---------
"Who doesn't work makes no errors"
@jakers
2^16 is 2 to the 16th power (= 65536).
If you've been wondering what the "x^y" key is on in the Microsoft Calculator's "scientific" view, here's your answer.
it was mentioned way above, but the majority of posters proved that they haven't read it, so here it goes again:
www.joelonsoftware.com/.../26b.html
Experienced similar problems tallying far lesser values on a modest library grant budget. After having to ascertain, cell by cell, whether correct formulas were used in currency conversion and network tariff costs and then modifying line by line, I finally resorted to my handy Casio fx-260 solar.
Oy!
For all the Open Office posts
If Open Office is so great why do you read this blog ?
***...
Well, playing around to see if I could get the numbers in question, I found the following:
A1 = 65535
A2 = A1 * 1.00000000001 = 65535 (yeah, floating point)
A3 = A2 * 1.00000000001 = 65535.9
A4 = A3 * 1.00000000001 = 0
A5 = A4 + 1 = 1
So there seems to be something more than just the representation going wrong here.
Example of the similar problem in Excel 2003 where the "displayed" value is the same but the HEX equivalents are wrong...
DEC 850 77.1 65535
HEX 352 4D FFFE
DEC 85 771 65535
HEX 55 303 FFFF
* although the displayed value is correct in both cases, the HEX values are off by 1 - (using DEC2HEX addin function)
When this settles down, please post the guilty code fragment.
It would be interesting to see how such an obscure bug could be written. There's probably a good example that can be (mis)quoted for years to come when describing coding practices.
(We all make mistakes, not a criticism of Microsoft. And cannot blame your QA for not testing all 2^64 floating point values.)
@Maria
Are you sure? I can't repoduce that at all
What version are you using?
Is that the right number of zeroes in 1.00000000001? How on earth do you get the .9 ?
@empower
I have resisted replying to the hordes in this thread and others claiming that DEC2HEX is broken, but since it seems to be getting so much repetition, let's all say it together:
"DEC2HEX converts a decimal _integer_ to hexadecimal"
It is not wrong that DEC2HEX(10.9)=A, and nor is it wrong that DEC2HEX(65,535-some small amount)=FFFE, since the operand is not an integer it gets truncated first.
Hi Guys
I've sorted Error traps for my Workbooks until sorted
Heres an example:
=IF(F8=850 & F9=77.1,F8*F9,F8*77.099999999)
Where F8 and F9 contain the offending calculation data
Seems to me like Microsoft can't figure out 65536 to me... Since it errors out when rows = > 65536. And with the previous poster saying the square root of 65536 is 256, the max number of columns.. Coincidence? Geesh...
Hi,
I have converted Excel's 97-2003 format Add-In
i.e. xyz.xla to 2007 format xyz.xlam. Now I am getting more no of columns and rows for xyz.xlam. Can I preserve 255 columns in xyz.xlam ???? I want the Addin's extention to .xlam, please help !!!
@***
I read this blog because at work my unimaginative employer has been trapped into a corporate licencing arrangement. Information on this and other bugs could affect my work so it is need to know.
At home I use open office or gnumeric. I dont think it's any accident that the big commercial product which is closed source (and raking in millions) falls prey to this kind of error while the open source product does not.
Cheers
Comments: (loading) Collapse