Cosine Function Returns Wrong Answer!

[Gordon Arnaut]

I have a problem with my Excel 2002 that is driving me nuts.

The cosine function returns the wrong answer for the cosine of 90 degrees.
The right answer is zero, of course, but Excel insists on returning a very
small number, 6e-17.

Cosine works fine for all other angles -- both smaller and larger than 90
degrees.

What the heck is going on? Even the simple little calculator that comes with
Windows gives the right answer.

Can anyone help?

Regards,

Gordon Arnaut
Ontario, Canada.

 

[Sandy Mann]

Excel uses the IEEE Standard for Double Precision Floating Point numbers
which is only accurate to 15 decimal places. Round the answer thus:

=ROUND(COS(A1/180*PI()),15)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Rick Rothstein \(MVP - VB\)]

I have a problem with my Excel 2002 that is driving me nuts.

The cosine function returns the wrong answer for the cosine of 90 degrees.
The right answer is zero, of course, but Excel insists on returning a very
small number, 6e-17.

Cosine works fine for all other angles -- both smaller and larger than 90
degrees.

What the heck is going on? Even the simple little calculator that comes
with
Windows gives the right answer.

Can anyone help?

Excel's trig functions don't work in Degrees, they work in Radians. Multiply
your Degrees by PI()/180 to convert them to Radians.

Rick

 

[Harlan Grove]

....
Excel's trig functions don't work in Degrees, they work in Radians.
Multiply your Degrees by PI()/180 to convert them to Radians.

Testing is good. It often prevents seriously flawed responses.

The formula =COS(90) returns -0.44807361612917, NOT 6.xxE-17. However, the
formula =COS(90*PI()/180) does return 6.1257422745431E-17.

As Sandy Mann already pointed out, this is due to rounding error. This is
one reason trig functions taking degrees (or grads) as arguments would have
made more sense than trig functions taking radians since trig functions
taking degrees or grads could have special-cased particular integer values
in order to return exact results.

This is common to all too many Excel functions. Even worse, GAMMALN doesn't
return 0 when passed 1 or 2 as arguments. There's much less excuse for that.

 

[Gordon Arnaut]

Wow, that was fast.

Thanks Sandy. That did the trick. Now I can stop pulling my hair out.


Rick,

It was doing the same thing in radians, but returning a slightly bigger
number, 3e-15.

With the ROUND command it now returns the zero if the input is in radians too.


Thanks for the help.

Regards,

Gordon.

 

[Gordon Arnaut]

Harlan,

Thanks for the heads up about GAMMMALN.

Rounding errors can be annoying. On a density altitude calculator I worked
up, it comes up with a rounding error of about a foot for every couple of
thousand feet increase in altitude. More of an annoyance than an impediment,
but still...

I spent a lot of time going through the formulas trying to get rid of it, to
no avail.

Microsoft needs to do a better job with this product.


Regards,

Gordon.

 

[JMB]

Microsoft needs to do a better job with this product.

Not impressed with the ribbon? <g>

 

[Harlan Grove]

Microsoft needs to do a better job with this product.

....

To be fair, spreadsheets are meant for financial calculations. They're
misused for general and especially scientific/engineering calculations.

If you believe you need to use a spreadsheet for scientific/engineering
calculations, you'd be well served to try out the Windows port of Gnumeric.
It's mathematical functions are much better implemented than Excel's in no
small part because they make use of code from the R Project and NetLib,
freely available code that Microsoft mey feel compelled to avoid due to
licensing terms they can't abide.

 

[dougaj4]

Harlan,

Thanks for the heads up about GAMMMALN.

Rounding errors can be annoying. On a density altitude calculator I worked
up, it comes up with a rounding error of about a foot for every couple of
thousand feet increase in altitude. More of an annoyance than an impediment,
but still...

I spent a lot of time going through the formulas trying to get rid of it, to
no avail.

Microsoft needs to do a better job with this product.

Gnumeric may do a better job with Gammaln, but it gives exactly the
same results as Excel with the Cos function.

I'd be interested to see how the errors in your density altitude
calculator came about. I don't see how an error of that magnitude
could be due to rounding at 16 significant figures, unless you are
subtracting very big numbers with a very small difference, in which
case any other standard PC-based package will suffer from similar
problems.

 

[Dana DeLouis]

Rounding errors can be annoying.

Hi. If it makes you feel better, even math programs don't return 0.' for
inexact input.

Excel:
=COS(PI()/2)
6.1257422745431E-17

Math Program:
Cos[Pi/2.]
6.123233995736766*^-17

Note the slightly more digits in the answer also then Excel's limited 15
digits.

This is a difference of ~~
2.50827..*^-20

I've always be curious as to what's going on under the hood.
Especially since the value of Pi in Excel has been reduced to 15 digits.
Instead of:
3.1415926535897932
Excel uses:
3.14159265358979

 

[Jerry W. Lewis]

Remember that what Excel uses and what it displays may be two different
things. The value Excel returns with PI() is 3.1415926535897931, as verified
by
=(PI()-884279719003555*2^-48)
which is the closest double precision approximation to the value of Pi, even
though Excel will natively display no more than 15 digits of it.

The exact value for cos(884279719003555*2^-49) is 6.1232339957367660E-17 (to
17 figures), which gets back to your question of what is going on under the
hood? I would presume that cosine is accurately implemented in the math
coprocessor, and that both Excel and your math program use it ...

FYI, it takes at least 21 decimal digits to distinguish the arccos of these
two cos values.

Jerry

Rounding errors can be annoying.

Hi. If it makes you feel better, even math programs don't return 0.' for
inexact input.

Excel:
=COS(PI()/2)
6.1257422745431E-17

Math Program:
Cos[Pi/2.]
6.123233995736766*^-17

Note the slightly more digits in the answer also then Excel's limited 15
digits.

This is a difference of ~~
2.50827..*^-20

I've always be curious as to what's going on under the hood.
Especially since the value of Pi in Excel has been reduced to 15 digits.
Instead of:
3.1415926535897932
Excel uses:
3.14159265358979

 

[Greg Lovern]

The cosine function returns the wrong answer for the cosine of 90 degrees.
The right answer is zero, of course, but Excel insists on returning a very
small number, 6e-17.

My Excel add-in, xlPrecision, returns exactly zero:

=xlpCOS(xlpRADIANS(90))

Returns:

0


That example omits the optional significant digits arguments, which
default to 100 significant digits if omitted. Depending on the edition
of xlPrecision, you can specifiy up to 2,147,483,648 (over 2 billion)
significant digits:

=xlpCOS(xlpRADIANS(90,2147483648),2147483648)


The free edition (use it as long as you like) of xlPrecision allows up
to 150 significant digits:

=xlpCOS(xlpRADIANS(90,150),150)


You can download the free edition here:

http://precisioncalc.com/Free.html


Thanks,

Greg Lovern
http://PrecisionCalc.com
More Power In Excel

 

[Harlan Grove]

(e-mail address removed) wrote...

Gnumeric may do a better job with Gammaln, but it gives exactly
the same results as Excel with the Cos function.

....

Unavoidable when arguments are in radians. Integer degrees are
rational fractions of PI in radians, and since PI is transcendental,
there's no hope for exact finite representation in any radix. And it's
much less reliable to try to special-case fractional values than
integer values.

FWIW, both SIN and COS get flaky near multiples of PI/2 where they
should be zero, but the other function gives +/-1. So if a few more
functions would be acceptable,

COS(x):
=IF(ABS(MOD(DEGREES(x),180)-90)>0.5,COS(x),
SIGN(COS(x))*SQRT(1-SIN(x)^2))

SIN(x):
=IF(ABS(MOD(DEGREES(x)-90,180)-90)>0.5,SIN(x),
SIGN(SIN(x))*SQRT(1-COS(x)^2))

 

[Dana DeLouis]

=(PI()-884279719003555*2^-48)

which is the closest double precision approximation to the value of Pi,
even
though Excel will natively display no more than 15 digits of it.

Hi. This is always an interesting subject. Just gee wiz is that this also
returns 0.0...
=PI()-80143857/25510582

--
Dana DeLouis

Remember that what Excel uses and what it displays may be two different
things. The value Excel returns with PI() is 3.1415926535897931, as
verified
by
=(PI()-884279719003555*2^-48)
which is the closest double precision approximation to the value of Pi,
even
though Excel will natively display no more than 15 digits of it.

The exact value for cos(884279719003555*2^-49) is 6.1232339957367660E-17
(to
17 figures), which gets back to your question of what is going on under
the
hood? I would presume that cosine is accurately implemented in the math
coprocessor, and that both Excel and your math program use it ...

FYI, it takes at least 21 decimal digits to distinguish the arccos of
these
two cos values.

Jerry

Rounding errors can be annoying.

Hi. If it makes you feel better, even math programs don't return 0.' for
inexact input.

Excel:
=COS(PI()/2)
6.1257422745431E-17

Math Program:
Cos[Pi/2.]
6.123233995736766*^-17

Note the slightly more digits in the answer also then Excel's limited 15
digits.

This is a difference of ~~
2.50827..*^-20

I've always be curious as to what's going on under the hood.
Especially since the value of Pi in Excel has been reduced to 15 digits.
Instead of:
3.1415926535897932
Excel uses:
3.14159265358979

 

[Jerry W. Lewis]

Hi. This is always an interesting subject. Just gee wiz is that this also
returns 0.0...
=PI()-80143857/25510582

But =(PI()-80143857/25510582) does not return zero. Your zero is a result
of the "optimization" introduced in Excel 97
http://support.microsoft.com/kb/78113
that causes subtractions of numbers that are equal to 15 decimal digits to
return zero, but only if that subtraction is the last operation. IMHO the
main result of that "optimization" has been to create more confusion.

Jerry

 

[Dana DeLouis]

... Your zero is a result

of the "optimization" introduced in Excel 97

Hi Jerry. Always an interesting subject of course. I am always trying to
learn.
Here's my old attempt at trying to understand Excel's Pi. It goes like
this:

Here's my Windows Machine's precision using Mathematica.

$MachinePrecision
15.954589770191003

Which is actually the correct representation, since
Log[2., 10]*MachinePrecision = 53.

If Excel' Pi is 15 digits, then the minimum ratio is:
(Note: my Rationalize is set at "Full" rationalize)

Rationalize[N[Pi, 15]]
80143857 / 25510582

And in fact, both
=-PI()+80143857 / 25510582
=PI()-80143857 / 25510582

return 0.0

There should not be a smaller ratio for Pi in Excel that can return 0.
Ok. So now I think that Excel's Pi is really set at 15 digits.

Then there is:

=SUM(-PI(),80143857 / 25510582)
=SUM(80143857 / 25510582,-PI())

which both return :
-4.44089209850063E-16

Which is as though Excel's Pi was at full machine Precision:

N[Pi, MachinePrecision] - 80143857 / 25510582
4.440892098500626*^-16

Which of course is:
2.^(-51)
4.440892098500626*^-16

So what this proves is that I still don't really know what's going on under
the hood. :>(