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.
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.
thanks for your answer.
but I think it would be easier to use GoogleSpreadsheets online. like Excel, they do not have this “bug”.
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”.
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.
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.
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’.
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)
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.
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 (
) to the left of the answer that solves your question.