We will be migrating from Ask to Discourse on the first week of August, read the details here

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

Here says: item 16 - cos - https://help.libreoffice.org/3.4/Calc...) "=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

edit retag close merge delete

( 2020-10-25 15:45:25 +0200 )edit

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

( 2020-10-25 17:15:47 +0200 )edit

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

( 2020-10-25 18:52:18 +0200 )edit

Thank you!

( 2020-10-25 19:13:29 +0200 )edit

Sort by » oldest newest most voted

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

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

more

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

( 2020-10-25 19:33:24 +0200 )edit

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.

more

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()).

( 2020-10-25 17:13:17 +0200 )edit

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.

( 2020-10-25 17:17:27 +0200 )edit

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 ?

( 2020-10-25 17:24:18 +0200 )edit

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.

( 2020-10-25 17:32:21 +0200 )edit

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.

( 2020-10-25 17:55:23 +0200 )edit

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.

( 2020-10-25 17:55:30 +0200 )edit

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.

( 2020-10-25 18:21:13 +0200 )edit

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.

( 2020-10-25 18:21:33 +0200 )edit

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

You are free to think that ...

( 2020-10-25 18:39:57 +0200 )edit

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-circ...

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 ...(more)

( 2020-10-25 18:57:38 +0200 )edit

Check Precision as shown (choose Tools - Options… - LibreOffice Calc - Calculate).

You can show until 15 decimals, and only will see zeros.

Tested with LibreOffice 6.4.7.2 (x86); OS: Windows 6.1.

Check the mark () to the left of the answer that solves your question.

more

An emergency exit .... download a Trigonometric Table, include it in the file and make use of the information with VLOOKUP, looking for the value in the Table.

more

thanks for your answer. but I think it would be easier to use GoogleSpreadsheets online. like Excel, they do not have this "bug".

( 2020-10-25 17:53:26 +0200 )edit

I do not see any practical difference between the value 6,12323399573677E-017 and zero, except perhaps for calculating orbital corrections for the Voyager 1 spacecraft at a distance of one or more light-years from the sun. And, given the way that computers work with numbers, I don't see this behaviour of Calc as a "bug".

( 2020-10-25 19:44:04 +0200 )edit

if you are working with matrix operations with very small numbers and another very small number show up in a place where it should be zero, it might cause some trouble.

( 2020-10-25 20:23:25 +0200 )edit

it seems that I made a mistake by saying that on Excel and on GoogleSpreadsheets they calculate the way I said. Apparently they do the same way Calc does. It is just a matter of configuration. sorry about that.

( 2020-10-25 20:30:33 +0200 )edit

Yes, but not just matrix operations! Any place where it is necessary to calculate the difference between similar numbers (not just close to zero) can be a source of large errors. In experimental physics, these situations are a major contributor to a phenomenon known as 'propagation of errors'.

( 2020-10-25 23:14:48 +0200 )edit

do you know if it is possible to do cos(90) [direct in degrees - without the conversion to rad] on Calc? Apparently when the software accepts the degree, there is no "very small=0" number issue. It is like this on Matlab cosd and on Excel I guess (I do not have Excel here to check)

( 2020-10-26 13:33:01 +0200 )edit

Not directly, at least in my version of LO (6.4.6.2). But there is the neat conversion function RADIANS(number of degrees) which converts degrees to radians, so you can avoid PI and just do COS(RADIANS(degrees)) ...

But then, of course, if degrees = 90, the value returned by the above formula is that old problem 6.12323399573677E-17.

There is no end to it.

( 2020-10-26 14:35:12 +0200 )edit