wrong calculation ?
 

Hello.

if i do the following calculation in excel 2007 i receive a wrong result.
= 850 * 77,1

is this solved in any patch ?

Thanks.

wrong calculation ?
 

This is a known bug in the display of numbers close to 65535.
Apparently a fix is on the way:

http://blogs.msdn.com/excel/archive/...ue-update.aspx



On Sep 28, 11:38 am, Andreas
wrote:
Hello.

if i do the following calculation in excel 2007 i receive a wrong result.
= 850 * 77,1

is this solved in any patch ?

Thanks.

wrong calculation ?
 

Hi Andreas,

what result?

Could be again a localization issue.

Try 77.1

Though, as a formula in a cell,
I don't get a wrong result but an error message.

US-Version !


--
Gruß

Helmut Weber, MVP WordVBA

"red.sys" & chr$(64) & "t-online.de"
Win XP, Office 2003 (US-Versions)

wrong calculation ?
 

Hi Andrew,

when talking about bugs,
how about that:

Dim lCnt As Long
lCnt = 850 * 39
' overflow

Maybe it just isn't my day,
or I should not have removed Visual Studio
from my harddrive,
but I needed the sectors which are still working. ;-)

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

wrong calculation ?
 

On Fri, 28 Sep 2007 13:22:24 +0200, Helmut Weber
wrote:

Hi Andreas,

what result?

Could be again a localization issue.

Try 77.1

Though, as a formula in a cell,
I don't get a wrong result but an error message.

US-Version !

Helmut,
It is a bug in Excel 2007 only.
--ron

wrong calculation ?
 

Dim lCnt As Long
lCnt = 850 * 39
' overflow

That's not a bug but rather an effect of the way VBA does calculations.
Since both 850 and 39 can be stored as Integers, VBA uses Integer data types
for calculation, converting the *result* of the calculation to a Long. In
general, VBA uses the smallest (least number of bytes) data types it can for
calculations. If you change one of the operands to a Long, everything will
work as expected.

Dim lCnt As Long
lCnt = 850 * 39&

This type of behavior is will known to programmers.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"Helmut Weber" wrote in message
...
Hi Andrew,

when talking about bugs,
how about that:

Dim lCnt As Long
lCnt = 850 * 39
' overflow

Maybe it just isn't my day,
or I should not have removed Visual Studio
from my harddrive,
but I needed the sectors which are still working. ;-)

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

wrong calculation ?
 

Try it this way:

Sub efg()
Dim lCnt As Long
lCnt = 850& * 39

End Sub

--
Regards,
Tom Ogilvy


"Helmut Weber" wrote:

Hi Andrew,

when talking about bugs,
how about that:

Dim lCnt As Long
lCnt = 850 * 39
' overflow

Maybe it just isn't my day,
or I should not have removed Visual Studio
from my harddrive,
but I needed the sectors which are still working. ;-)

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

wrong calculation ?
 

Hi Chip,

thank you very much.

It's a pleasure to learn from you.

I wonder,
how the guys and girls in the Word private groups
will comment my question.

Cheers

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

wrong calculation ?
 

Thanks for the blog.


"Andrew Taylor" wrote:

This is a known bug in the display of numbers close to 65535.
Apparently a fix is on the way:

http://blogs.msdn.com/excel/archive/...ue-update.aspx



On Sep 28, 11:38 am, Andreas
wrote:
Hello.

if i do the following calculation in excel 2007 i receive a wrong result.
= 850 * 77,1

is this solved in any patch ?

Thanks.

wrong calculation ?
 

Hi Tom,

yes, I know by now.

It's not that I didn't get
the right answer in the private Word group.

Guys and girls are pretty smart there as well.

Have a nice day.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

wrong calculation ?
 

When I answered, Chip's answer was not visible. I was not aware you were
crossposting and don't subscribe to the word newsgroup. Sorry if my answer
was a source of irritation.

--
Regards,
Tom Ogilvy



"Helmut Weber" wrote:

Hi Tom,

yes, I know by now.

It's not that I didn't get
the right answer in the private Word group.

Guys and girls are pretty smart there as well.

Have a nice day.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

wrong calculation ?
 

Hi Tom,

... Chip's answer was not visible. I was not aware you were
crossposting and don't subscribe to the word newsgroup.
Sorry if my answer was a source of irritation.

no irritation at all, no reason to be sorry,
just another correct answer.

Thank you.

Have a nice day.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

wrong calculation ?
 

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

"Andreas" wrote:

Hello.

if i do the following calculation in excel 2007 i receive a wrong result.
= 850 * 77,1

is this solved in any patch ?

Thanks.