Bug in Excel 2007

  • Thread starter Thread starter Molham Serry
  • Start date Start date
EDIT: Sorry, I was just being stupid.. I took a closer look at a
description of the INT function and it does say that it will round
DOWN. So when I add more decimal spaces A4 now displays 4.999999.. so
I guess it works as it's supposed to. Sorry for fudgeup.. >_<
Click to expand...

I presume you meant to put A1 - A2 in the third cell :-)

What you're experiencing there isn't necessarily a bug, it's more a
limitation of floating point arithmatic and is a VERY common issue with
computers in general. Because of the way numbers are stored they are not
always stored exactly. Do a google search on floating point errors if you're
interested, there isn't much point in my rewriting everything. I should add
the IMO it is a bug and they probably should have used a format that doesn't
have this problem.
 
Michael C said:
There'd be a range of skills I imagine from genius to very good.
Click to expand...

Yes, but given the likely age distribution and what that would imply
about the likely CS courses available while they were still students,
their assembler skills and bit manipulation experience likely runs
from modest to nonexistent.

Being an excellent C++ programmer is NOT ipso facto proof of expertise
in machine language programming, especially not with
 
Harlan Grove said:
Yes, but given the likely age distribution and what that would imply
about the likely CS courses available while they were still students,
their assembler skills and bit manipulation experience likely runs
from modest to nonexistent.
Click to expand...

I totally disagree with that. While I'm sure that there are some programmers
on the Office team that know little about assembler I'm certain there would
be more than enough who have excellent knowledge. I work in high level
languages such as C# and VB6 and 50% of the people I work with know
assembler reasonably well (i've written my own 32 bit integer maths routines
for an 8 bit processor in assembler from scratch). Considering the assembler
knowledge here I would be absolutely amazed if that knowledge was lacking in
this particular MS project. This is one of the largest companies on earth
and this is one of their major products. Do you really think a multi-billion
dollar project is going to be lacking in expertise?
Being an excellent C++ programmer is NOT ipso facto proof of expertise
in machine language programming, especially not with
Click to expand...

I notice you put the words "bit manipulation" in your first statement but
missed it from this one. Naturally while C++ does not mean the programmer
will have any knowledge of assembler it does generally require the
programmer to have good knowledge of bit manipulation.
 
Here is another hint that it is probably a rendering problem.
Click to expand...

We have conclusive proof it is not a rendering problem. Look at the
example of "A1 + 1" and "A1 - 1". One gives the correct result and one
does not. If it was a rendering issue, they would both give correct
results.

My bet is they have two internal ways of storing the value, one for
values less than or (sometimes) equal to 65,535 and one for values
greater than or (sometimes) equal to 65,535.

That is, they have no consistent rule for which representation to set/
use when the value is equal to 65,535.

DS
 
We have conclusive proof it is not a rendering problem. Look at the
example of "A1 + 1" and "A1 - 1". One gives the correct result and one
does not. If it was a rendering issue, they would both give correct
results.

My bet is they have two internal ways of storing the value, one for
values less than or (sometimes) equal to 65,535 and one for values
greater than or (sometimes) equal to 65,535.

That is, they have no consistent rule for which representation to set/
use when the value is equal to 65,535.

DS
Click to expand...

rewriting the formual mathematically seems to get different result.
The problem may be due ot MSF is trying to swich 10 based floating
number to binary more "efficiently".

A1=(=850*77)
A2=(=850*0.1)
A3=(=A1+A2)=65535 This gets the correct number

A1=849*77.1+77=65534.900000
A2=0.1
A3=(=A1+A2)=100000 This is incorrect.

MS must be offseting the number at certain point get better results.
 
We have conclusive proof it is not a rendering problem. Look at the
example of "A1 + 1" and "A1 - 1". One gives the correct result and one
does not. If it was a rendering issue, they would both give correct
results.

My bet is they have two internal ways of storing the value, one for
values less than or (sometimes) equal to 65,535 and one for values
greater than or (sometimes) equal to 65,535.

That is, they have no consistent rule for which representation to set/
use when the value is equal to 65,535.

DS
Click to expand...

If it isn't a rendering problem why does the date show correctly for
day number 65535, even when it displays 100000 when formatted as a
number?
 
If it isn't a rendering problem why does the date show correctly for
day number 65535, even when it displays 100000 when formatted as a
number?
Click to expand...

It's precisely because it's treated differently in different calculations
that it can't be ONLY a rendering problem.
 
Harlan Grove said:
It's precisely because it's treated differently in different calculations
that it can't be ONLY a rendering problem.
Click to expand...

AFAIK it's not treated differently in different calculations. In all cases
the correct value is used for a formula. It's just the same rendering bug
shows up when rendering the results from those formula. See my reply to you
in the other thread.
 
Simply when you try to multiply 850 by 77.1 excel display the result to be
100000 !!!
Click to expand...

http://
img256.imageshack.us/img256/6360/excelzu5.th.jpg

Funny, but as you look at real value you have 77,1000000000012 instead
od 77,1 where you get good value.

I put 77,1111 in first row then 77,1110 in following to get values
every 0,0001 but step from 77,1106 to 77,1105 is weird becuase I get
77,1105000000001 instead of 77,1105... then 77,1096000000002.
77,1087000000003 and so one...
 
Simply when you try to multiply 850 by 77.1 excel display the result to be
100000 !!!
Click to expand...

http://
img256.imageshack.us/img256/6360/excelzu5.th.jpg

Funny, but as you look at real value you have 77,1000000000012 instead
od 77,1 where you get good value.

I put 77,1111 in first row then 77,1110 in following to get values
every 0,0001 but step from 77,1106 to 77,1105 is weird becuase I get
77,1105000000001 instead of 77,1105... then 77,1096000000002.
77,1087000000003 and so one...
Click to expand...


[/
URL] funny
 
The numerical result is actually
0.009999999999990910.
Click to expand...

year, that's why you need to check inside an epsilon region for
equalness (the = sign should be replaced by a function, which tests
something like x \in [y - machine-epsilon, y + machine-epsilon]
this happens everywhere where you do floating point calculations on a
CPU.

a good and quick solution would be to calculate everything in cents
(i.e. integers!) and no commas at all.
 
Oddly, I also multiplied 2 * 10.2 * 3212.5 and got 100000, so it is
not just when you multiply 2 numbers that equal 65535, it is when
certain combinations of number add up to 65535...

Also tried 2 * 10.2 * 642.5 * 5 and it is 100000
Click to expand...

OTOH, when I tried 5 * 10.2 * 642.5 * 2 I got the correct answer,
65535.
 
try this in Excel 2007 to enable the cascading effect of the bug...

A1: =TEXT(850*77.1,"#,##0.00") 10000
A2: =A1+1 10001
A3: =A2+1 10002
A4: =A3+1 10003

May be due to the fact that TEXT function grabs the value of the cell
from its rendered value rather than the value in memory?
 
There appears to be more of them. For example:
=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
..etc
Click to expand...

Is this only happen for multiplication and for number with fractions?
 
Is this only happen for multiplication and for number with fractions?

According to the microsoft link that (e-mail address removed) posted,
(
http://blogs.msdn.com:80/excel/archive/2007/09/25/calculation-issue-update.aspx )

it has to do with rounding of the 16th bit, which is just outside Excel's
display of 15 digits.
Hence, at the same 16 digit precision, numbers like the following display
this issue:

=POWER(20333622017/79428817,2)

=PRODUCT(103, 229, 647, 11939, 35969)/100000000000

etc...
 
Back
Top