44 posts

850 * 77.1 is 65535... unless you're Microsoft Excel.

Back to Forum: Coffeehouse
• AndyC wrote:
﻿
 Minh wrote: ﻿ It's actually provable that it's not just a display bug: A1: =850*77.1 A2: =ROUND(A1,1)+1 A3: =A2+1 Result in A3 = 100002

Since that only seems to happen when rounding, it suggests there is another subtle bug in ROUND that only surfaces when the displayed value doesn't match the stored value.

Or that the display bug is actually a result of a bug in their rounding routine (as displayed values are always rounded to the 15th decimal place).

• Maybe you guys aren't thinking the bug isn't in the calculation as such, its in the cell being able to show the result of the value correctly.. the +1 thing is just +1 to whatever the REAL value of the 100000 cell is..

The whole concept of decimal in binary systems is tainted, its just ~how~ tainted it is..

Still, pretty bad bug to release..

• AndyC wrote:
﻿
Since that only seems to happen when rounding, it suggests there is another subtle bug in ROUND that only surfaces when the displayed value doesn't match the stored value.

ROUND() is burden by how it's usually used, that is to represent something to the nearest dollars, for example:

`A1 = 0.95   B1 = ROUND(A1) = 1A2 = 0.95   B2 = ROUND(A2) = 1            B3 = SUM(B1:B2) = 2`

In B3, people don't want to see 1.9 -- and even though, you could as easily ROUND(B3)... people want the SUM of B1 : B2 to be the natural SUM of what they see.

So, somehow, ROUND bakes the translated value into the cell... in this case, the value is translated incorrectly.

So, any function that requires baking the translated value into the cell, I think would be subject to this bug.

How embarassing.

• BlackTiger wrote:
﻿
And he is wrong about "only a display bug"...

Even before the Excel blog was posted, some slashdotters had already demonstrated that this is a display-only bug .  See this slashdot sub-thread:
And yet it works FINE when you GRAPH it...

Most of the time people don't read cells themselves, rather automated processes perform spreadsheet calculations, update database with the results, update spreadsheets, etc.  Humans normally look at prepared graphs, which apparently isn't affected by this problem.  But this is still very bad for cases where human eyes are looking directly at the cells to read data.

• Escamillo wrote:
﻿
 BlackTiger wrote: ﻿ And he is wrong about "only a display bug"...

Even before the Excel blog was posted, some slashdotters had already demonstrated that this is a display-only bug .  See this slashdot sub-thread:
And yet it works FINE when you GRAPH it...

Most of the time people don't read cells themselves, rather automated processes perform spreadsheet calculations, update database with the results, update spreadsheets, etc.  Humans normally look at prepared graphs, which apparently isn't affected by this problem.  But this is still very bad for cases where human eyes are looking directly at the cells to read data.

Is it your religion? [6] ("Doctor Who (2005)", season 2, "Satan's pit")

If you stumbled and fell down, it doesn't mean yet, that you're going in the wrong direction.
• WHERE IS FIX?!?!?

If you stumbled and fell down, it doesn't mean yet, that you're going in the wrong direction.
• Still no fixes for this "display only bug"? Not quite "display"? Or "by design"?

If you stumbled and fell down, it doesn't mean yet, that you're going in the wrong direction.
• UPDATE RELEASED!

- Excel 2007
- 64-bit Excel Services 2007
- 32-bit Excel Services 2007

KB Articles have been posted as well:

- Excel 2007
- Excel Services 2007

If you stumbled and fell down, it doesn't mean yet, that you're going in the wrong direction.