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

Ask Your Question
0

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

asked 2020-10-25 15:14:16 +0200

d4mx gravatar image

updated 2020-10-25 20:08:59 +0200

LeroyG gravatar image

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 flag offensive close merge delete

Comments

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

d4mx gravatar imaged4mx ( 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.

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

Thank you!

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

4 Answers

Sort by » oldest newest most voted
1

answered 2020-10-25 19:14:20 +0200

Zizi64 gravatar image

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

=ROUND(COS(PI()/2);10)
edit flag offensive delete link more

Comments

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

d4mx gravatar imaged4mx ( 2020-10-25 19:33:24 +0200 )edit
3

answered 2020-10-25 16:03:13 +0200

ajlittoz gravatar image

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.

edit flag offensive delete link more

Comments

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

d4mx gravatar imaged4mx ( 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.

ajlittoz gravatar imageajlittoz ( 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 ?

d4mx gravatar imaged4mx ( 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.

ajlittoz gravatar imageajlittoz ( 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.

d4mx gravatar imaged4mx ( 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.

Opaque gravatar imageOpaque ( 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.

d4mx gravatar imaged4mx ( 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.

erAck gravatar imageerAck ( 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 ...

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

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

d4mx gravatar imaged4mx ( 2020-10-25 18:57:38 +0200 )edit
0

answered 2020-10-25 18:48:55 +0200

LeroyG gravatar image

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.


Add Answer is reserved for solutions.

Press edit below your question if you want to add more information; also can comment an answer.

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

edit flag offensive delete link more
0

answered 2020-10-25 17:49:56 +0200

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.

edit flag offensive delete link more

Comments

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

d4mx gravatar imaged4mx ( 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".

ve3oat gravatar imageve3oat ( 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.

d4mx gravatar imaged4mx ( 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.

d4mx gravatar imaged4mx ( 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'.

ve3oat gravatar imageve3oat ( 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)

d4mx gravatar imaged4mx ( 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.

ve3oat gravatar imageve3oat ( 2020-10-26 14:35:12 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-10-25 15:14:16 +0200

Seen: 197 times

Last updated: Oct 25 '20