Bug in Excel 2007
Molham Serry
100000 !!!
Bernard Liengme
best wishes
--
Bernard Liengme
Excel MVP
http://people.stfx.ca/bliengme
"Molham Serry" <mse...@contact.com.eg> wrote in message
news:834DAE15-EE9C-4996...@microsoft.com...
Dana DeLouis
Even Sumproduct returned 100000.
=SUMPRODUCT(850,77.1)
=SUMPRODUCT(850,77.1,2,0.5)
(2*0.5 = 1)
This seems Major to me!
--
Dana DeLouis
Windows XP & Excel 2007
"Molham Serry" <mse...@contact.com.eg> wrote in message
news:834DAE15-EE9C-4996...@microsoft.com...
Wild Bill
plugged in enough to call & get someone out of their bed or their bong.
Earl Kiosterud
I wonder what that might have to do with it. I haven't installed 2007 yet (prescient?), so
I wonder if anyone tried other numbers that should produce the same answer, like 4 *
16383.75, or 222 * 295.2027?
--
Regards from Virginia Beach,
Earl Kiosterud
www.smokeylake.com
"Bernard Liengme" <blie...@stfx.TRUENORTH.ca> wrote in message
news:%23miNyEV$HHA....@TK2MSFTNGP03.phx.gbl...
Bernard Liengme
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email
"Molham Serry" <mse...@contact.com.eg> wrote in message
news:834DAE15-EE9C-4996...@microsoft.com...
Dana DeLouis
=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
What's even stranger is this: Suppose the formula is in A1.
=A1+1 returns 100001, which appears to show the formula is in fact 100000
and a very Serious problem.
And if you multiply be say, 2 you get something else:
=A1*2
returns 131070, as if A1 had 65535. (which it should have been)
=A1*1
Keeps it at 100000.
=A1-1 returns 65534
=A1/1 is still 100000
=A1/2 retuns 32767.5
Using MAX() on a range appears not to see 100000.
Very Serious!
--
Dana DeLouis
"Molham Serry" <mse...@contact.com.eg> wrote in message
news:834DAE15-EE9C-4996...@microsoft.com...
Dana DeLouis
=40000.223
=41000.348
=52000.723
Such numbers are converted to:
=40000.2229999999
=41000.3479999999
=52000.7229999999
--
Dana DeLouis
"Molham Serry" <mse...@contact.com.eg> wrote in message
news:834DAE15-EE9C-4996...@microsoft.com...
EpsilonRho
Gene
Marcus Schöneborn
> I also see that Excel 2007 still has this over 10+ year bug still in it:
>
> =40000.223
> =41000.348
> =52000.723
>
> Such numbers are converted to:
>
> =40000.2229999999
> =41000.3479999999
> =52000.7229999999
That isn't a bug, but well-known floating point representation
inaccuracy. FPUs don't work in decimal.
The question just is whether Excel should work with a FPU's floating
point type, or make its own and calculate "exactly" in it. The latter
would be much slower, so it's doubtful if it is really worth the major
slowdown this would cause.
OTOH, Windows Calculator did get changed away from FPU's own floating
point types to its own arbitrary precision type. But it's only
calculating one cell at once...
Dana DeLouis
Hi. I believe Microsoft still considers it a "Problem." (ie Bug)
It affects numbers between 2^15 - 2^16 (32768 - 65536) that end with:
{.098, .223, .348, .473, .598, .723, .848, .973}
(Note: the endings are offset by 1/8)
--
Dana DeLouis
"Marcus Schöneborn" <div...@googlemail.com> wrote in message
news:slrnffd3h3....@hagger.rbi.informatik.uni-frankfurt.de...
Marcus Schöneborn
> > That isn't a bug, but well-known floating point representation
>
> Hi. I believe Microsoft still considers it a "Problem." (ie Bug)
> It affects numbers between 2^15 - 2^16 (32768 - 65536) that end with:
>
> {.098, .223, .348, .473, .598, .723, .848, .973}
>
> (Note: the endings are offset by 1/8)
If they really get turned into
40000.2229999999
it is an actual bug because:
as float: 40000.22265625000000000000
as double: 40000.22299999999813735485
as long double: 40000.22299999999999897682
Excel: 40000.2229999999 (rounded down)
so this would be LESS precision than IEEE doubles, or somewhat broken
rounding.
If there is an additional 98, it's no bug and a consequence of IEEE
doubles.
ilia
None of the other n-byte integers of all 1s have this issue.
A1 =((2^16-1)/425)*(850/2)
B1 = 65,535
C1 =(A1=B1)
C1 displays TRUE.
In VBE, typing Debug.Print ActiveCell.Value yields the correct
result. Likewise, the Application.Evaluate() method yields the
correct result.
This code (in worksheet's code module):
Public Sub testBug()
Me.Range("D5").Value = 850
Me.Range("D6").Value = 77.1
Debug.Print _
Application.WorksheetFunction.SumProduct(Me.Range("D5"),
Me.Range("D6"))
End Sub
Also displays 65,535.
Curious bug indeed!
On Sep 22, 11:44 pm, "Earl Kiosterud" <some...@nowhere.com> wrote:
> Sheesh! The correct answer, 65,535, happens to have two bytes worth of 1 digits in binary.
> I wonder what that might have to do with it. I haven't installed 2007 yet (prescient?), so
> I wonder if anyone tried other numbers that should produce the same answer, like 4 *
> 16383.75, or 222 * 295.2027?
> --
> Regards from Virginia Beach,
>
> Earl Kiosterudwww.smokeylake.com
>
> "Bernard Liengme" <blien...@stfx.TRUENORTH.ca> wrote in message
>
> news:%23miNyEV$HHA....@TK2MSFTNGP03.phx.gbl...
>
>
>
> > Confirmed!
> > best wishes
> > --
> > Bernard Liengme
> > Excel MVP
> >http://people.stfx.ca/bliengme
>
> > "Molham Serry" <mse...@contact.com.eg> wrote in message
> >news:834DAE15-EE9C-4996...@microsoft.com...
> >> Simply when you try to multiply 850 by 77.1 excel display the result to be 100000 !!!- Hide quoted text -
>
> - Show quoted text -
Dana DeLouis
this bug.
Sub Bug()
Dim x
[A1].Formula = "=77.1*850"
x = Range("A1").Value * 2
x = Range("A1").Value2 * 2
'// This is 200000 !!!
x = Range("A1").Text * 2
End Sub
Even numbers like this display this issue:
=SUMPRODUCT(0.41,65535/0.41)
--
Dana DeLouis
"ilia" <iasa...@gmail.com> wrote in message
news:1190645599.8...@50g2000hsm.googlegroups.com...
Dana DeLouis
Numbers Ending in .848 Appear Incorrectly
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q161234
STATUS
Microsoft has confirmed this to be a problem in the Microsoft products
listed at the beginning of this article. We are researching this problem ...
...Apparently since Microsoft Excel 5.0 <vbg>
--
Dana DeLouis
"Marcus Schöneborn" <div...@googlemail.com> wrote in message
news:slrnffdgat....@hagger.rbi.informatik.uni-frankfurt.de...
Earl Kiosterud
So does 2003. It appears to be a 2007 issue.
--
Regards from Virginia Beach,
Earl Kiosterud
www.smokeylake.com
"EpsilonRho" <Epsil...@nospam.ooo> wrote in message
news:b5wJi.51485$Um6....@newssvr12.news.prodigy.net...
*alan*
the article mentioned below, it's stated that the incorrect representation
of numbers ending in .848 occurs between "32,768 and 65,535". Isn't 65,535
the product that's getting misrepresented as 100000 when you calculate
850*77.1 in Excel2007?
Perhaps they were finally attempting to address the previous bug, and just
mucked it up even more?
--
alan
"Dana DeLouis" <ddel...@bellsouth.net> wrote in message
news:ucrJihs$HHA....@TK2MSFTNGP03.phx.gbl...
Harlan Grove
>There appears to be more of them. For example:
Makes it appear this is a rendering issue.
>What's even stranger is this: Suppose the formula is in A1.
>=A1+1 returns 100001, which appears to show the formula is in fact
>100000
>and a very Serious problem.
>And if you multiply be say, 2 you get something else:
>
>=A1*2
>returns 131070, as if A1 had 65535. (which it should have been)
...
> =A1-1 returns 65534
...
Almost makes it appear some calculations use the .Text property rather
than the .Value property of A1. If so, definitely a bug.
Amusing, though
Harlan Grove
"Dana DeLouis" <ddelo...@bellsouth.net> wrote...
>There appears to be more of them. For example:
...
Makes it appear this is a rendering issue.
>What's even stranger is this: Suppose the formula is in A1.
>=A1+1 returns 100001, which appears to show the formula is in fact
>100000
>and a very Serious problem.
>And if you multiply be say, 2 you get something else:
>
>=A1*2
>returns 131070, as if A1 had 65535. (which it should have been)
...
> =A1-1 returns 65534
...
Almost makes it appear some calculations use the .Text property rather
than the .Value property of A1. If so, definitely a bug.
Amusing, though, that A1+1 would be odd while A1-1 would be even.
Actually, I'd guess someone was trying to rewrite the 15 decimal
digits truncation code but screwed up, perhaps missing a bitwise AND
or XOR. I wonder how many programmers on the Excel team have any
experience with assembler? I'd guess not many, if any.
>Using MAX() on a range appears not to see 100000.
Meaning that if A1 contained this false 100000 and B1 contained 5,
MAX(A1:B1) returns 5? Or A2 contained 70000 and MAX(A1:A2) returns
70000?
>Very Serious!
Definitely. How soon do you suppose they'll fix this? We should start
a pool. I'll take not until SP2, which, based on SP1's release date
(not yet), I'd guess would be mid-Fall 2008.
wils...@gmail.com
FP "bugs" abound. I wonder why Excel doesn't use BCD for its
calculations? Since the primary use of Excel is to calculate money,
and money is decimal....
oh wait. I just answered my own question. Rounding to 2 decimal places
generally eliminates that problem.
meg...@gmail.com
when working with floating point numbers, you are only guaranteed a
number of significant digits (varies depending on the size of the data
type). In this case, your numbers are good up to 7 digits (NNNNN.NN),
which fits with the double data type in C.
michael...@gmail.com
> This is simple rounding error. Down at the hardware level of PCs,
> when working with floating point numbers, you are only guaranteed a
> number of significant digits (varies depending on the size of the data
> type). In this case, your numbers are good up to 7 digits (NNNNN.NN),
> which fits with the double data type in C.
Complete and utter BS. As pointed out, the Excel representation holds
absolutely nothing in common with either the computers native FP
result, nor with the IEEE standard for floating point.
--
MB
Michael C
news:BWXJi.55186$YL5....@newssvr29.news.prodigy.net...
> It may be an insignificant coincidence, but I found it interesting that in
> the article mentioned below, it's stated that the incorrect representation
> of numbers ending in .848 occurs between "32,768 and 65,535". Isn't
> 65,535 the product that's getting misrepresented as 100000 when you
> calculate 850*77.1 in Excel2007?
> Perhaps they were finally attempting to address the previous bug, and just
> mucked it up even more?
Bugs are usually found on boundaries. In this case 65535 is the smallest
number that fits into 16bits and 65536 requires 32 bit. Although excel will
use floating point for the numbers so it might not apply in this case there
is likely some significance.
Michael
Michael C
news:1190686202....@19g2000hsx.googlegroups.com...
> Actually, I'd guess someone was trying to rewrite the 15 decimal
> digits truncation code but screwed up, perhaps missing a bitwise AND
> or XOR. I wonder how many programmers on the Excel team have any
> experience with assembler? I'd guess not many, if any.
I'd be EXTREMELY suprised if not one person or even only a few on the excel
team had experience with assembler. Remember all these programmers would be
using C which has all the power of assembler with the ease of use of
assembler.
Michael
Michael C
news:1190691703....@r29g2000hsg.googlegroups.com...
> On Sep 24, 10:11 pm, mega...@gmail.com wrote:
>> This is simple rounding error. Down at the hardware level of PCs,
>> when working with floating point numbers, you are only guaranteed a
>> number of significant digits (varies depending on the size of the data
>> type). In this case, your numbers are good up to 7 digits (NNNNN.NN),
>> which fits with the double data type in C.
>
> Complete and utter BS. As pointed out, the Excel representation holds
> absolutely nothing in common with either the computers native FP
> result, nor with the IEEE standard for floating point.
Are you saying excel doesn't use the FP processor? How then did the previous
intel floating point bug appear in excel?
Michael
Harlan Grove
><michael.e.br...@gmail.com> wrote in message
...
>>Complete and utter BS. As pointed out, the Excel representation
>>holds absolutely nothing in common with either the computers native
>>FP result, nor with the IEEE standard for floating point.
>
>Are you saying excel doesn't use the FP processor? How then did the
>previous intel floating point bug appear in excel?
Excel uses the FPU for INTERMEDIATE calculations, but the final value
of any cell formula gets passed through some, er, MSFT value-added
processing before it becomes the cell's value. This is how
nonparenthesized expressions are implicitly rounded to 15 decimal
digits. Also, and quite unfortunately, some built-in function calls
perform additional and usually unnecessary postprocessing of FPU
results, e.g., MOD(2^30-1,3), which returns #NUM! because MOD can't
handle arguments that produce quotients comfortably within 32-bit
integer or 64-bit double precision bounds even though this is well
within the FPU's capabilities.
I don't think this is the case for 850*77.1. I suspect some bright
spark on the Excel development team decided to rewrite their machine
representation-to-human representation internal number formatting
function, and they screwed it up somehow.
What really concerns me is what Dana DeLouis pointed out: with A1
containing =850*77.1, =A1+1 returns 100001 while =A1-1 returns 65534.
That's VERY, VERY BAD because it implies the value in A1 isn't just
fed directly to the FPU stack. It's subjected to, er, value-added
preprocessing which seems to differ depending on what the precise
operation is. What I'm most worried about, given Dana's other
examples, is whether someone thought they were doing Excel users a
favor by treating increment (+1), decrement (-1), idempotent
operations (+0, *1, ^1), binary left shift (*2) and binary right shift
(/2) as special cases in the formula parser. MSFT *DID* rewrite parts
of the formula parser in XL2007, which is how they lifted the 7 nested
function call limit etc. I'd bet they rewrote too much of it.
Sharath
> Simply when you try to multiply 850 by 77.1 excel display the result to be
> 100000 !!!
Guess this bug was introduced recently.
I use Excel 2002 (10.6823.6626) at work and it shows the answers
correctly as 65535.
-Sharath
Jerker Pihl
> Simply when you try to multiply 850 by 77.1 excel display the result to be
> 100000 !!!
With an old office xp excel I get the correct answer :-D
Regards
el0
Harlan Grove
...
> . . . with the ease of use of assembler.
Indeed!
And when it comes to bitwise manipulation, LESS clarity than assembler.
agni.asgeirsson
> Excel 2000 gives the correct answer.
> Gene
Excel 2003 Sp2 gives the correct answer for 850*77.1, but when I
format tha number as Hex with Dec2Hex I get FFFE which is one off!.
Dec2Hex(850*77.1+1) results FFFF but Dec2Hex(850*77.1+2) results the
correct value of 10001.
Agni
Peter Ross
> Simply when you try to multiply 850 by 77.1 excel display the result to be
> 100000 !!!
I wonder if this could be related to another oddity of Excel, present
in 2007 and in various earlier versions.
Choose a number x, use Excel to calculate the STDEV of the numbers x
+1, x+2, .., x+10. The correct
answer should be 3.027650354.. However, there are various jackpot
values of x that give other answers.
For example, x=9989999999223 gives a STDEV around 123575.8667. If you
vary that x slightly, say by adding
0.05, the STDEV changes to other very large values.
Excel seems capable of working with these sorts of large numbers; for
the above x, calculate y = 49*x*x
and then z = SQRT(y)/7 and you recover x correctly. The problem does
not seem to be due to an earlier
flaw, now fixed by Microsoft, of using a poor choice of method of
calculating STDEV.
At a guess, it appears to be an issue to do with internal change of
representation.
dou...@gmail.com
cell containing the erroneous result, e.g.
Function testmult(val) As Double
testmult = val.Value + 1
End Function
This will return 100001 (when declared as a double, variant, or
unspecified), but if you step through it VBA at least knows that it is
really 65536.
Also if you declare it as a Single it will return the correct result
(65536)
michel...@gmail.com
> <michael.e.br...@gmail.com> wrote in message
There is a biggest bug in excel!!!! See:
http://www.microsfot.it/index.php?view=article&catid=8%3Ablog-attualita&id=105%3Amicrosoft-excel-2007-sbaglia-le-moltiplicazioni&option=com_content&Itemid=18
The article is written in italian but click on the link (you must be a
user for the site) and download the examples. INCREDIBLE!!! It's a old
bug but microsoft hasn't patched!!! Bah!!!
Christopher McNabb
> Simply when you try to multiply 850 by 77.1 excel display the result to be
> 100000 !!!
This bug does not appear to be in Excel 2004 for the Mac.
minhtam...@gmail.com
a massive slowdown, as most people rely on it here and won't be happy
to hear about this. And just when IT was pushing so hard to get it
through.
Cactus77
2003 is 65.536.
Is it just a case of coincidence or......????
:)
joec...@gmail.com
Simon Murphy
http://smurfonspreadsheets.wordpress.com/2007/09/25/excel-2007-calculation-bug-test-code/
compares .text to .value2
please chip in if you find an error or have an improvement
cheers
Simon
"ilia" <iasa...@gmail.com> wrote in message
news:1190645599.8...@50g2000hsm.googlegroups.com...
jonmo...@gmail.com
> Simply when you try to multiply 850 by 77.1 excel display the result to be
> 100000 !!!
Multiplying 666 by 98.4009009009009 wrongly gives 100000. Could this
be the work of the devil himself and not Microsoft's fault at all?
jim...@gmail.com
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
Diamontina Cocktail
> 100000 !!!
>
>
Adding to this mystery, I did a simple calc like this:
A1 cell had =65535/850 and answer was 77.1
B1 cell had the number 850 in it.
C1 cell had =A1*B1 and the answer was 100000
D1 had =C1-65535 and answer was 0
C1 answer was 100000 and you subtract 65535 from that and Excel 2007 says
the answer is 0.
Hmmm....
AlleyT...@gmail.com
> Simply when you try to multiply 850 by 77.1 excel display the result to be
> 100000 !!!
This is calculated correctly in open office
grebori...@eurofasteners.com
> Simply when you try to multiply 850 by 77.1 excel display the result to be
> 100000 !!!
I've verified is exact!!
Erwin Oosterhoorn
=5.1*12850+1 : 100001
=5.1*12850+2 : 65537
but if you do
=5.1*12850+2-1 : 65536
=5.1*12850+2-2 : 65535
nick.m...@gmail.com
purpletr...@gmail.com
> Simply when you try to multiply 850 by 77.1 excel display the result to be
> 100000 !!!
The interesting thing is that not every multiplier of 65535 provides
the 100000 answer. 4*16383.75 and 750*87.38 provided correct answers
and held up when running through the operations (+1 -1 etc) mentioned
earlier in the thread.
guru...@gmail.com
Simon Murphy
Link to .txt now added at bottom of that post.
http://smurfonspreadsheets.wordpress.com/2007/09/25/excel-2007-calculation-bug-test-code/
cheers
Simon
"Ed Hansberry, MS-MVP/Mobile Devices"
<spamfree-...@hotmail.spambegone.com> wrote in message
news:b81if31no751n8pdb...@4ax.com...
> "Simon Murphy" <tobinSim...@codematic.net> wrote:
>
>>Rough VBA code to test for potential problems posted here:
>>
>>http://smurfonspreadsheets.wordpress.com/2007/09/25/excel-2007-calculation-bug-test-code/
>>
>>compares .text to .value2
>>please chip in if you find an error or have an improvement
>
> Unrunable. All ' and " marks have to be changed because your web
> page changed them. :-(
>
> Looks interesting though. Perhaps putting it in a .txt download.
> --
> __________________________________________________________________________________
> Ed Hansberry (Please do *NOT* email me. Post here for the benefit of all)
> What is on my Pocket PC? http://www.ehansberry.com/
> Microsoft MVP - Mobile Devices www.pocketpc.com
> What is an MVP? - http://mvp.support.microsoft.com/
jmma...@gmail.com
> Simply when you try to multiply 850 by 77.1 excel display the result to be
> 100000 !!!
FYI, I do not experience this problem using OpenOffice Calc 2.1.
Entering this in a cell:
=850*77.1
correctly returns 65535.
Joe Marton
gymna...@gmail.com
> Simply when you try to multiply 850 by 77.1 excel display the result to be
> 100000 !!!
I am using office 2003 and i don't seem to have that problem.
jeaco...@gmail.com
> Simply when you try to multiply 850 by 77.1 excel display the result to be
> 100000 !!!
No you are wrong. when you the SUM function this is not the case.