Cosine of 90 degrees

[mikelee101]

Hello All,
I was just curious why I wasn't getting zero for the result of the cosine of
a 90 degree angle. This formula

=COS(RADIANS(90))

returns

6.12574E-17

when I would have expected 0. Am I misusing a function in there somewhere?
Any thoughts would be greatly appreciated.

Excel 07, XPPro SP3.

Thanks.

 

[Bernard Liengme]

This is a common occurrence: expecting zero and getting very close to it. It
all has to do with IEEE 754 and the way Excel (and moth other computer apps)
convert decimal numbers (numbers using base 10) to binary numbers (numbers
using base 2).

Only a problem with real numbers (i.e. numbers with decimal places) not with
integers

How to avoid: round to 12 decimal places =ROUND(COS(RADIANS(90)),12)
You handheld calculator does this automatically, so you never see it with
that machine

For a full explanation:
Chip's clear explanation
http://www.cpearson.com/excel/rounding.htm

Floating-point arithmetic may give inaccurate results in Excel
http://support.microsoft.com/kb/78113/en-us

(Complete) Tutorial to Understand IEEE Floating-Point Errors
http://support.microsoft.com/kb/42980

What Every Computer Scientist Should Know About Floating Point
http://docs.sun.com/source/806-3568/ncg_goldberg.html
http://www.cpearson.com/excel/rounding.htm

Visual Basic and Arithmetic Precision
http://support.microsoft.com/defaul...port/kb/articles/Q279/7/55.ASP&NoWebContent=1

Others:
http://support.microsoft.com/kb/214118

http://docs.sun.com/source/806-3568/ncg_goldberg.html

best wishes

 

[Shane Devenshire]

Hi,

1. Computers work in binary is part of the explanation.
2. The algorithm used doesn't address 0 specifically, which may have been
corrected in 2010.
3. You can always apply ROUND(COS(RADIAN(A1)),10)

 

[alanglloyd]

Hello All,
I was just curious why I wasn't getting zero for the result of the cosineof
a 90 degree angle. �This formula

=COS(RADIANS(90))

returns

6.12574E-17

when I would have expected 0. �Am I misusing a function in there somewhere? �
Any thoughts would be greatly appreciated.

Excel 07, XPPro SP3.

I expect its because 0.5 * Pi has no exact binary representation, so
its only approximate. Its accuracy is only to the 63-bit accuracy of
the maths calculations in the processor.

I think that 2**-63 is not far off 10**-17.

Alan Lloyd

 

[alanglloyd]

How to avoid: round to 12 decimal places =ROUND(COS(RADIANS(90)),12)
You handheld calculator does this automatically, so you never see it with
that machine

<snip>

12 decimal places would give you an accuracy of about 6 inches / 150mm
in measuring the distance between the earth & the sun - good enough
for government work.

Alan Lloyd

 

[JoeU2004]

I was just curious why I wasn't getting zero for the result of the
cosine of a 90 degree angle. This formula
=COS(RADIANS(90))

I understand your expectation. Even though binary arithmetic often causes
subtle differences in results (e.g. IF(10.1-10=0.1,TRUE) returns FALSE!),
many implementations of transcendental and other math functions that are
implemented with approximating formulas make special cases for recognizable
boundary conditions.

For example, that is probably why COS(PI()) is exactly -1.

Since RADIANS(90) returns exactly the same binary result as PI()/2, and
PI()/2 is exactly the binary representation of PI() divided by 2, there is
no reason why the COS() implementation cannot make a special case of
COS(RADIANS(90)) and return exactly zero.

But that's a judgment call. Obviously, the implementors have to draw a line
somewhere.


----- original message -----

 

[alanglloyd]

I understand your expectation. �Even though binary arithmetic often causes
subtle differences in results (e.g. IF(10.1-10=0.1,TRUE) returns FALSE!),
many implementations of transcendental and other math functions that are
implemented with approximating formulas make special cases for recognizable
boundary conditions.

For example, that is probably why COS(PI()) is exactly -1.

Since RADIANS(90) returns exactly the same binary result as PI()/2, and
PI()/2 is exactly the binary representation of PI() divided by 2, there is
no reason why the COS() implementation cannot make a special case of
COS(RADIANS(90)) and return exactly zero.

But that's a judgment call. �Obviously, the implementors have to draw a line
somewhere.

In fact bits 11 & 12 of the 8087 (Maths Processor) Control Word has a
setting for that . . .

Bits 11 & 12 of 8987 Control Word
RC- rounding control
00 = round to nearest or even
01 = round towards -infinity
10 = round towards +infinity
11 = truncate towards zero

.. . . and that is easily set & reset in software. The COS() function
should really have implemented that. Possibly on an MS "To Do" list
which has been forgotten <g>.

Alan Lloyd