"Molham Serry" <mse...@contact.com.eg> wrote in message
news:834DAE15-EE9C-4996...@microsoft.com...
=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...
Earl Kiosterud
www.smokeylake.com
"Bernard Liengme" <blie...@stfx.TRUENORTH.ca> wrote in message
news:%23miNyEV$HHA....@TK2MSFTNGP03.phx.gbl...
"Molham Serry" <mse...@contact.com.eg> wrote in message
news:834DAE15-EE9C-4996...@microsoft.com...
=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...
=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...
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...
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...
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.
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 -
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...
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...
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...
"Dana DeLouis" <ddel...@bellsouth.net> wrote in message
news:ucrJihs$HHA....@TK2MSFTNGP03.phx.gbl...
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
"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.
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.
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
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
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
Are you saying excel doesn't use the FP processor? How then did the previous
intel floating point bug appear in excel?
Michael
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.
Guess this bug was introduced recently.
I use Excel 2002 (10.6823.6626) at work and it shows the answers
correctly as 65535.
-Sharath
With an old office xp excel I get the correct answer :-D
Regards
el0
Indeed!
And when it comes to bitwise manipulation, LESS clarity than assembler.
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
> 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.
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)
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!!!
This bug does not appear to be in Excel 2004 for the Mac.
:)
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...
Multiplying 666 by 98.4009009009009 wrongly gives 100000. Could this
be the work of the devil himself and not Microsoft's fault at all?
Also tried 2 * 10.2 * 642.5 * 5 and it is 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....
This is calculated correctly in open office
I've verified is exact!!
but if you do
=5.1*12850+2-1 : 65536
=5.1*12850+2-2 : 65535
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.
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/
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
I am using office 2003 and i don't seem to have that problem.
No you are wrong. when you the SUM function this is not the case.
65535 is also, coincidentally, the rather arbitrary limit on the # of
rows in an xls file
(has this changed with 2007?).
A very interesting "error" value in the cell is correct you just can
not see it:
It would appear that this defect is more an prank than an error.
850 77.1 100000
Charting and or simple math of cell "100000" below reveals the correct
number!
x2
850 77.70000 66045 1 132090
850 77.60000 65960 2 131920
850 77.50000 65875 3 131750
850 77.40000 65790 4 131580
850 77.30000 65705 5 131410
850 77.20000 65620 6 131240
850 77.10000 100000 7 131070
850 77.00000 65450 8 130900
850 76.90000 65365 9 130730
850 76.80000 65280 10 130560
850 76.70000 65195 11 130390
850 76.60000 65110 12 130220
Sneakiing up on the number works.
850 77.1 100000
+/- .00001
850 77.1000000000 65535.0000
850 77.09999 65534.9915
850 77.0999800000 65534.9830
850 77.1 100000
850 77.0999999999999000 65535
Doesn't matter whether the .xls file is originally created in Excel
2007 or in Excel 2003.
OpenOffice works for me.
This is a very strong indication that the whole thing is a rendering
issue. There are other major issues with rendering: the Data Table
mechanism sometimes does not display the correct result, but the result
"one step earlier". Just scrolling down and up will change the displayed
value to the the correct result.
Harlan Grove wrote:
> "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
>
>
>> Using MAX() on a range appears not to see 100000.
>>
>> Very Serious!
>> --
>> Dana DeLouis
The number of rows in a .xls file (not a limit, all worksheets have
them) is 65536, not 65535.
It's not particularly arbitrary, either - 65536 is 2^16, or the maximum
integer it's possible to represent in two bytes.
Yes, the number of rows has changed in XL07.
In article <1190734252.1...@d55g2000hsg.googlegroups.com>,
Presumably "Render like Excel 2007" will appear soon as a feature
like the leap year in Microsoft 1900 date calculation scheme in Excel.
Microsoft QA program has been subverted by the 6000 page OOXML
specification. There are lots of unique features in that spec also.
Desktop monopoly subverts number system natural monopoly!!
I am not disagreeing here however the leap year bug was concocted in Lotus
and Excel
copied it to be compatible. Of course that was when Lotus had a monopoly
--
Regards,
Peo Sjoblom
Lotus never had a monopoly. Certainly Lotus Development Corp was never
convicted of having acted as a monopoly, as Microsoft has. 123 never
had the market share Excel now does. SuperCalc, its own Symphony (not
the OOo 1.x clone IBM is now foisting), Framework, Enable, Javelin,
Boeing Calc, Quattro (before Pro), and let's not forget Mosaic Twin
and VP-Planner each had enough market share to be noticed in the
computer press. The last two had enough market share that Lotus sued
them in the look & feel lawsuits, protecting their precious character
mode interface that became effectively worthless about 3 years after
the decision with the debut of Excel 5.
When XL was created (on the Mac, based on Multiplan), the Mac's 1904
system was used which avoided the problem. It was reintroduced when XL
was ported to the PC.
In article <#CoMP54$HHA....@TK2MSFTNGP06.phx.gbl>,
I was told by a co-worker that Microsoft already has a fix, but they
have only released it in-house and to a high-level MSDN subscriber
category. I can't find anything on their public website, however.
Chris Van Brederode
Bad wording on my part, maybe I should have used "de facto" monopoly in
windows
or maybe just dominating spreadsheet. I take it back.
--
Regards,
Peo Sjoblom