Strange numeric problem

  • Thread starter Thread starter Billy
  • Start date Start date
B

Billy

If I type =ARCSIN((75^2-75*86,6)/(75*(75-86,6))) it will get "unvalid"
ARCSIN((75*(75-86,6)/(75*(75-86,6))) and
ARCSIN((75^2-75*86,6)/(75^2-75*86,6))) are shown as valid and ok.

75^2-75*86,6 and 75*(75-86,6) are both displayed as -870,000000... with 30
decimals exact.
If I change 86,6 to 86,5, or 75 to 76 in the first formula then the result
will be valid and ok.

When I made a list of the first formula rolling 86,6 between 80-90, the
error didn't
show up at 86,6, but at 86,3 and 86,8 for instance (?!)

Is this a numeric storage problem ?
Strange in that case since there are not many decimals involved.
 
Billy said:
Is this a numeric storage problem ?
Click to expand...

Yes. That is, it is an issue with how Excel represents numbers internally
and performs calculation, namely using binary floating point. Consequently,
most numbers with decimal fractions cannot be represented exactly; and that
often results in inexact results of calculations.

Strange in that case since there are not many decimals involved.
Click to expand...

The number of decimal places has little to do with it. Consider this
example: IF(10.1 - 10 = 0.1, TRUE). It will return FALSE (!).

But IF(ROUND(10.1 - 10, 2) = 0.1, TRUE) returns TRUE as expected. That
might give you a clue as to how to solve these problems in general.

In your case, reordering constants and operations leads to small differences
in the results.

75^2-75*86.6 results in exactly -870. But 75*(75-86.6) results in
exactly -869.999999999999,54525264911353588104248046875 internally.

Note that 75*86.6 is 6495 exactly, where as 75-86.6 is
exactly -11.5999999999999,94315658113919198513031005859375 internally.

Note: Being from US, I use period (".") to separate integer and decimal
fraction. I am using comma (",") here to demarcate the first 15 significant
digits, which is all Excel will format, rounding the 16th significant digit.

You should decide what degree of precision is right for your problem (often
depends on measurement accuracy and other factors). Then use ROUND() to
explicitly round expressions to that number of decimal places, where it
makes sense to do so. For example:

ROUND(75^2-75*86.6,1) / ROUND(75*(75-86.6),1)

does result in exactly 1.


----- original message -----
 
Hallo Billy,

Dies ist eine englisch-sprachige Newsgroup...

Ich empfehle einen "Wrapper":
=ASIN(ROUND((75 ^ 2-75 * 86.6) / (75 * (75-86.6)),15))

Das Problem liegt in der Charakteristik von Excel's Gleitkommanutzung.

Siehe auch Punkt 8 meiner Excel Don'ts:
http://sulprobil.com/html/excel_don_ts.html

Viele Grüße,
Bernd
 
Hello,

Sorry - I must somehow have triggered an auto-translate...

I suggest a wrapper:
=ASIN(ROUND((75 ^ 2-75 * 86.6) / (75 * (75-86.6)),15))

The problem resides in Excel's floating point representation:
See also point 8 of my Excel Don'ts, please:
http://sulprobil.com/html/excel_don_ts.html

Regards,
Bernd
 
Back
Top