CALC: Cosine of 90° and sine of 180° are different from zero

Here says: item 16 - cos - Mathematical Functions - LibreOffice Help) “=COS(PI()/2) returns 0, the cosine of PI/2 radians”

Actually, when I try:
=COS(PI()/2) is returning 6,12323399573677E-017 and
=SIN(PI()) is returning 1,22464679914735E-16
How to solve this? It should return zero.

Does it happen to you as well?

My theory is that Calc only use 15 characters for PI.

Version: 6.1.5.2 Build ID: 1:6.1.5-3+deb10u6 CPU threads: 4; OS: Linux 4.19; UI render: default; VCL: gtk3; Locale: pt-BR (pt_BR.UTF-8); Calc: group threaded

Duplicate of CALC: Cosseno de 90 e seno de 180 diferente de zero. Cosine of 90 ° and sine of 180 ° are different from zero

sorry. It is my first time using this forum. I thought the portuguese question would go only for portuguese responders and vice-versa.

Hello @d4mx: Questions posted at https://ask.libreoffice.org/c/brazilian-portuguese/9 are seen by users who enter at https://ask.libreoffice.org/c/brazilian-portuguese/9. But if you click in an user name, you could see all his/her posts.

Thank you!

Functions like COS() or SIN() are computed with a polynomial approximation called “limited development”. You have first a quotient PI()/2 which is itself approximate. You then cumulate two approximations. If you’re versed in mathematics, you can evaluate the final error resulting from inexact argument, method error (truncated polynomial development) and computation itself (every operation is inexact and mapped to the “nearest” representable value).

Never trust floating-point computation, it will never give an exact result. If the algorithm is long enough (which is not the case for COS() and SIN()), the result may be really far away from what math tells.

In your case 10-16 is fairly close to zero. The main point is how this magnitude compares to other quantities it may be added to. If their orders of magnitude are “sufficiently” different, the tiny “false zero” does not matter. What is important is the usage you put to this “false zero”.

LO by itself cannot “correct” the small quantity to zero because it can’t assert that the argument is strictly equal to pi/2 because equality has no meaning with floating-point.

To show the community your question has been answered, click the ✓ next to the correct answer, and “upvote” by clicking on the ^ arrow of any helpful answers. These are the mechanisms for communicating the quality of the Q&A on this site. Thanks!

In case you need clarification, edit your question (not an answer which is reserved for solutions) or comment the relevant answer.

Thanks for your answer. I understand it.
When Calc developers will solve this uncomfortable issue of sin(pi)=0 and cos(pi/2)=0 ? This is an exact solution. It did not seem hard to correct it.
Why this problem did not appear on Microsoft Excel?
This “problem” is only a problem when sine and cosine are equal to zero (for angles >= pi/2). For the other sines and cosines there is absolutely not a problem regarding the approximation.
Depending on the application, the fact that the cosine of pi / 2 is different from zero (even though it is very small) can cause problems and avoidable errors in the results.
Is there any way for me to fix it myself? I need an exact Zero for COS(PI()/2) and SIN(PI()).

If you need an exact value, don’t write COS(PI()/2) (if this is what you wrote) but 0.0. This avoids all computations, it is faster and accurate.

Use COS(x) only when the value of x is not known beforehand.

Cos(pi/2) is different from Cos(0)

if you mean type manually 0 on the several cells that should return me the correct answer 0? What about my automation and time gain ?

Maybe you can use Edit>Find & Replace to bulk replace the subexpression. I didn’t check the possibility. It’s up to you to decide if you favour accuracy or formula readability.

Thanks for your time answering it.
But in my opinion, it is about time for the Calc developers solve this issue that do not appear on Excel or GoogleSpreadsheets.

When Calc developers will solve this uncomfortable issue of sin(pi)=0 and cos(pi/2)=0 ?

Probably never since the result is a result of IEEE Standard for Floating-Point Arithmetic (IEEE 754) and pure mathematics and numerical mathematics (performed by computers) sometimes yield different results, due to the fact, that number representation in computers suffers from finite numbers of bits while pure mathematics has a notion of infinity.


> But in my opinion ...

If this is your opinion, file an enhancement request at bugzilla, but I’m pretty sure you’ll get a WONTFIX response.

I think it would make it much easier for all users working with trigonometry in Calc to have an exact answer for COS (PI / 2) and SEN (PI). Take as an exemple Microsoft Excel and GoogleSpreadsheets that already did it, and I think it had nothing to do with this IEE 754 standard.

uncomfortable issue of sin(pi)=0 and cos(pi/2)=0 ? This is an exact solution

No, it is not. Already PI() does not return an exact value of Pi but only a near value representable in an IEEE 754 double floating point within a limited precision, so SIN() is not called with an exact value of Pi.

Why this problem did not appear on Microsoft Excel

If so, then because they “optimized” it to lie about it in this case, treating near values of (multiples of) Pi special.

and I think it had nothing to do with this IEE 754 standard.

You are free to think that …

Thanks for your answer.

I am not talking about of pi (and other sins-cosines results), which is an irrational number like SQRT(2). I understand the computer could not process a number with infinite digits, it have to limit it.

I am talking ONLY about the result of sin (pi) and cos (pi/2) which is, according to Math I know, exactly zero. www.mathsisfun.com/geometry/unit-circle.html

Take it for the example SQRT(2)*SQRT(2)=2, in this case the result matched because the computer gave the result of SQRT(2)=1,4142135623731 with sufficient digits to make the operation SQRT(2)*SQRT(2) give an “exact” result.
In addition, if the computer give the result of SQRT(2)=1,41421356 (with less digits), the result of SQRT(2)*SQRT(2) would not be 2 anymore, it would be 1,99999999328787, a “not exact result”.

I am only a basic Calc user questioning, as a customer, an ease way to solve the issue I posted. Issue that has already been solved in Excel and GoogleSpreadsheets.

Again - Calc calculates using numerical mathematics and not using special a-priori knowledge about the desired result. And I know the results in pure mathematics and as @erAck also tried to express: Google and Excel are hiding what their computers achieve as well, just to avoid discussions like this one.

“the result of sin (pi) and cos (pi/2) which is, according to Math I know, exactly zero.”

It is a theoretical result. It is true, when you use the exact value of the Pi. But the computers (and nobody else) can not know/use the exact value of the Pi in a calculation. The only one solution is the rounding.

That is a possible question: who must round the result? The programmer, when he/she creates a Calc function or the User, when he/she uses a Calc function…?

The next question: how many decimal digits must use a Programmer, when he/she creates a trigonometric function? The answer: it is better to left to the User. The programmer must use the best result of the double precision (or any other used) number representation.

Use the function ROUND(). A 6…10 decimal digit precision (maybe) enough for you:

=ROUND(COS(PI()/2);10)

thanks!
actually I chose a 14 decimal digit and got the Zero and precision I needed.