Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
answer =850*77.1 formula in excel 2007 is 100000 , WHY?
answer =850*77.1 formula in excel 2007 is 100000 , WHY?
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
answer =850*77.1 formula in excel 2007 is 100000 , WHY?
answer =850*77.1 formula in excel 2007 is 100000 , WHY?
That, my friend, is a bug... plain and simple. It looks like it requires floating point numbers, where at least one of the numbers is a constant, to trigger it; but not all combination bring out the bug. For example, =8500*7.71 will not produce it whereas 450*154.2 will. If you put these numbers (or the ones you posted originally) into cells, say A1=850 and B1=77.1, then =A1*B1 does not produce the bug... at least one of numbers being multiplied appears to have to be a constant... =A1*77.1 does produce the bug. Oh, and the reason I said it requires floating point numbers is because =85*771 returns the correct answer. By the way, everyone of the above products has 65535 as the "proper" answer; and, of course, this value is =2^16-1 (which does not produce the bug), and which is the largest signed 16-bit value. Rick |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
answer =850*77.1 formula in excel 2007 is 100000 , WHY?
.....and which is the largest UNsigned 16-bit value.
Rick |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
answer =850*77.1 formula in excel 2007 is 100000 , WHY?
If you really want to know all the details see: http://www.lomont.org/Math/Papers/20...cel2007Bug.pdf |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
answer =850*77.1 formula in excel 2007 is 100000 , WHY?
This is a known bug that has been discussed ad nauseum for the past
month or more. A hotfix is available at http://support.microsoft.com/?kbid=943075 "Description of the Excel 2007 hotfix package: October 9, 2007". piknik wrote: answer =850*77.1 formula in excel 2007 is 100000 , WHY? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
answer =850*77.1 formula in excel 2007 is 100000 , WHY?
As I am sure you didn't discover this yourself, I suggest you read the
extensive discussion in newsgroups and elsewhere about this topic since it was reported. You could start with a Google search of the relevant Excel newsgroups? -- David Biddulph "piknik" wrote in message ... answer =850*77.1 formula in excel 2007 is 100000 , WHY? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
answer =850*77.1 formula in excel 2007 is 100000 , WHY?
Microsoft recently published a patch that appears to fix this
http://support.microsoft.com/kb/943075 Oddly, this patch (though already out) was not bundled with the "essential" patches that I downloaded last week along with the trial version of Office 2007. Values of 2^16-1-d (whether as a formula result or a constant), where d was too small (2^-37 <= d <= 6*2^-37) to properly impact the 15-digit decimal representation, displayed as 100000 despite still having the correct underlying value. Values of 2^16-d displayed as 100001 despite still having the correct underlying value. Interestingly, this seems to have been a new intersection in Excel 2007 of two old bugs that have existed at least since version 4, and probably since the inception of Excel. 1. There appears to have been a set of millions of valid binary numbers (that included fractional parts) which for whatever reason were not permitted as constant values in Excel, but were supported as the result of calculations. The values like this that I am aware of rounded away the trailing bits in the final three positions of a binary floating point number. For values like 0.5 +/- d, this rounding made a perverse kind of sense as an early attempt at the "optimization" that was introduced in 1997 http://support.microsoft.com/kb/78113 which "optimization" has led to numerous questions where a formula that by itself appears to return zero doesn't behave like zero in a LOOKUP or IF function or in a larger formula (because at the binary level, the result is not and should not be zero). This rounding made less sense with numbers like, 0.500001220703125026645352591003756970167160034179 6875+/-d, where even the "rounded" number could not be fully displayed in 15 decimal digits. This longstanding bug appears to have been completely fixed in the original production release of 2007, before application of the current patch. 2. There appears to have been a non-overlapping (AFAIK) set of millions of decimal fractions that could not be displayed properly http://support.microsoft.com/kb/161234 admits to x.848 displaying as x.8479999999 for x an integer between 2^15 and 2^16, but there are millions of other decimal fractions that were similarly mis-displayed http://groups.google.com/group/micro...2d9f986ce8e65b I was not previously aware of any number in this set whose incorrect display was off by more than 1 in the 15th digit; as a result, fixing this bug has seemed to have little or no priority with MS until now. I believe both of these longstanding bugs to be related to the current bug for the following reasons: - It does not make sense that a current change to the display engine capable of causing this current bug could have survived its testing phase without uncovering this bug. - If the process of displaying results (formulas as well as constants) first went through the filter of bug 1 before being passed to the display engine, then the 2007 patch for bug 1, would mean that display of these impacted values had never been tested, yet the need to test their display could easily have been overlooked. - The patch for the current problem appears to also fully patch bug 2, while preserving the patch for bug 1 (thank you MS for not simply restoring bug 1). Jerry "piknik" wrote: answer =850*77.1 formula in excel 2007 is 100000 , WHY? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Solver Internal Error When Generating Answer Report | Excel Discussion (Misc queries) | |||
excel 2007 850*77.1=100000 | Excel Discussion (Misc queries) | |||
850*77.1=100000 ?! (Excel 2007) | Excel Discussion (Misc queries) | |||
Excel 2007- formula =SUM(850*77.1) returns incorrect answer = 1000 | Excel Discussion (Misc queries) | |||
=YEAR(3/7/2007) in EXCEL, get the answer as 1900. Why? | Excel Worksheet Functions |