joeu2004 wrote on Thu, 24 Jan 2008 10:11:40 -0800 (PST):

j On Jan 24, 5:28 am, "James Silverton"
j wrote:
?? Jerry wrote on Wed, 23 Jan 2008 20:27:37 -0800:
JWL For instance, =ROUND(10^14+0.5,0)
JWL incorrectly returns the value of 100000000000000
??
?? This is all very interesting but ultimately what does it
?? matter if ROUND sometimes goes in the wromg direction at
?? the limits of precision? It is hard for me to imagine a
?? real case where so many figures are necessary but I am
?? ready to be instructed.

j It isn't so much that anyone needs this much precision, but
j that these flaws cause problems in formulas that involve
j large numbers. The explanation is more precise than the
j solution requires.

j I presented this anomaly when I was having a problem with
j INT(). Actually, the problem is with MOD(x,y), which is
j well-know to an error with large numbers. I wanted to use x
j - y*INT(x/y) work around the problem. When that was
j misbehaving, I did a binary search and discovered the 10^14
j boundary and speculated the relationship to the number of
j significant digits that Excel will display.

Again without wishing to start a fight, can you give me an
example of a real calculation where the rounding error makes a
difference to a conclusion? I probably wouldn't use Excel if
there were such a case since it's not that hard to program in
double precision or the like.

James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.verizon.not

I had an issue once in a Life insurance calculation. In one of the calculation rules we had to round to a whole year. Each year
(of duration) had its own cost factor.
It turned out there were problems because one algorithm established the unrounded duration as 5.49999999999999999 years and the
other one as 5.5.
A whole year of difference after rounding, with a large impact on customer's revenues.

Just an example.

I keep repeating we need (optional) decimal arithmetic in Excel, like scaled integers in VB. There is no way the misinterpretation
I explained above could have come up in decimal arithmetic. Because we're all used to it and are more familiar with the pitfalls,
like repeating fractions.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel



"James Silverton" wrote in message ...
| joeu2004 wrote on Thu, 24 Jan 2008 10:11:40 -0800 (PST):
|
| j On Jan 24, 5:28 am, "James Silverton"
| j wrote:
| ?? Jerry wrote on Wed, 23 Jan 2008 20:27:37 -0800:
| JWL For instance, =ROUND(10^14+0.5,0)
| JWL incorrectly returns the value of 100000000000000
| ??
| ?? This is all very interesting but ultimately what does it
| ?? matter if ROUND sometimes goes in the wromg direction at
| ?? the limits of precision? It is hard for me to imagine a
| ?? real case where so many figures are necessary but I am
| ?? ready to be instructed.
|
| j It isn't so much that anyone needs this much precision, but
| j that these flaws cause problems in formulas that involve
| j large numbers. The explanation is more precise than the
| j solution requires.
|
| j I presented this anomaly when I was having a problem with
| j INT(). Actually, the problem is with MOD(x,y), which is
| j well-know to an error with large numbers. I wanted to use x
| j - y*INT(x/y) work around the problem. When that was
| j misbehaving, I did a binary search and discovered the 10^14
| j boundary and speculated the relationship to the number of
| j significant digits that Excel will display.
|
| Again without wishing to start a fight, can you give me an
| example of a real calculation where the rounding error makes a
| difference to a conclusion? I probably wouldn't use Excel if
| there were such a case since it's not that hard to program in
| double precision or the like.
|
| James Silverton
| Potomac, Maryland
|
| E-mail, with obvious alterations:
| not.jim.silverton.at.verizon.not
|

Niek wrote on Thu, 24 Jan 2008 20:03:50 +0100:


NO
had an issue once in a Life insurance calculation. In one of
the calculation rules we had to round to a whole year. Each year
(of duration) had its own cost factor.
It turned out there were problems because one algorithm
established the unrounded duration as 5.49999999999999999 years
and the
other one as 5.5.
A whole year of difference after rounding, with a large impact
on customer's revenues.
NO
NO Just an example.

NO I keep repeating we need (optional) decimal arithmetic in
NO Excel, like scaled integers in VB. There is no way the
NO misinterpretation I explained above could have come up in
NO decimal arithmetic. Because we're all used to it and are
NO more familiar with the pitfalls, like repeating fractions.

Now that example is interesting! Can such troubles be avoided by
adding a trivial constant like 0.5 x10^-13 where a round is
expected? I know there are problems in scientific work where two
large numbers need to be subtracted and matrices become singular
but I would not have expected it in Excel used for its normal
purposes.

James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.verizon.not