Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cosine of 90 degrees
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. -- Mike Lee McKinney,TX USA |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cosine of 90 degrees
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/default...NoWebContent=1 Others: http://support.microsoft.com/kb/214118 http://docs.sun.com/source/806-3568/ncg_goldberg.html best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "mikelee101" <mikelee101athotmaildotcom wrote in message ... 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. -- Mike Lee McKinney,TX USA |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cosine of 90 degrees
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) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "mikelee101" wrote: 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. -- Mike Lee McKinney,TX USA |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cosine of 90 degrees
On Sep 14, 7:24�pm, mikelee101 <mikelee101athotmaildotcom wrote:
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. 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cosine of 90 degrees
On Sep 14, 7:53�pm, "Bernard Liengme" wrote:
<snip 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cosine of 90 degrees
"mikelee101" <mikelee101athotmaildotcom wrote:
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 ----- "mikelee101" <mikelee101athotmaildotcom wrote in message ... 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. -- Mike Lee McKinney,TX USA |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cosine of 90 degrees
On Sep 14, 8:21�pm, "JoeU2004" wrote:
"mikelee101" <mikelee101athotmaildotcom wrote: 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. 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you convert decimal degrees to degrees minutes seconds | Excel Discussion (Misc queries) | |||
how can i convert degrees/minutes/seconds to decimal degrees? | Excel Discussion (Misc queries) | |||
calculate from degrees to degrees/minutes/seconds on excel? | Excel Worksheet Functions | |||
convert decimal degrees to degrees minutes seconds | Excel Discussion (Misc queries) | |||
converting from digital degrees to degrees minutes seconds | Excel Worksheet Functions |