Bug 137744

Summary:	CALC: SIN(PI()) and COS(PI()/2) results are different from zero
Product:	LibreOffice	Reporter:	d4mx <dbdecastro>
Component:	Calc	Assignee:	Not Assigned <libreoffice-bugs>
Status:	RESOLVED NOTABUG
Severity:	normal
Priority:	medium
Version:	6.1.5.2 release
Hardware:	All
OS:	All
Whiteboard:
Crash report or crash signature:		Regression By:
Attachments:	A sample from MS Excel 2016, from which screenshot in comment 1 was taken An actual Excel spredsheet, not the screenshot that I accidentally attached in comment 2 :-)

Description d4mx 2020-10-25 18:22:35 UTC

Description:
CALC: =SIN(PI()) and =COS(PI()/2) results are different from exact zero.
Depending on the application and complexity, the fact that the cosine of pi/2 or sine of pi is different from zero (even though it is very small) can cause problems and avoidable errors in the results.

Steps to Reproduce:
1. open Calc and select a cell;
2. type =SIN(PI()) or =COS(PI()/2) and press enter;
3. the results should be exact Zero

Actual Results:
=COS(PI()/2) is returning 6,12323399573677E-017 
and =SIN(PI()) is returning 1,22464679914735E-16 

Expected Results:
=COS(PI()/2) should return 0
=SIN(PI()) should return 0


Reproducible: Always


User Profile Reset: No



Additional Info:
all other sines and cosines >= pi/2 that should return exact Zero do not return.
The problem related in this report do not happen on Excel or GoogleSpreadsheets.

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

Comment 1 Mike Kaganski 2020-10-25 18:33:48 UTC

This is not a bug. See FAQ [1], and also see how other major spreadsheet software handles that the same way: https://imgur.com/cEUYphr

[1] https://wiki.documentfoundation.org/Faq/Calc/Accuracy

Comment 2 Mike Kaganski 2020-10-25 18:42:24 UTC

Created attachment 166704 [details]
A sample from MS Excel 2016, from which screenshot in comment 1 was taken

(In reply to d4mx from comment #0)
> The problem related in this report do not happen on Excel or
> GoogleSpreadsheets.

This is wrong.
In Google Sheets, the default format does not show the small values; but applying the custom number format, you may see the same values: https://docs.google.com/spreadsheets/d/1ZQ69JN1kCbkXy1VM3B02Zek_NWc3AHDexgVOA48nNDA/edit?usp=sharing

In MS Excel, the attached document created in Excel 2016 didn't even require some manual formatting, and displayed the values right upon formula entry in a clean document. The same for Excel Online, which uses latest MS code (so is not an issue with "not current" version 2016 used on my desktop): https://1drv.ms/x/s!AqRfhRdisQhQg7hBswbWoVdFancMgQ?e=AtjGw4

Comment 3 Mike Kaganski 2020-10-25 18:45:56 UTC

Created attachment 166705 [details]
An actual Excel spredsheet, not the screenshot that I accidentally attached in comment 2 :-)

Comment 4 d4mx 2020-10-25 19:15:21 UTC

(In reply to Mike Kaganski from comment #2)
> Created attachment 166704 [details]
> A sample from MS Excel 2016, from which screenshot in comment 1 was taken
> 
> (In reply to d4mx from comment #0)
> > The problem related in this report do not happen on Excel or
> > GoogleSpreadsheets.
> 
> This is wrong.
> In Google Sheets, the default format does not show the small values; but
> applying the custom number format, you may see the same values:
> https://docs.google.com/spreadsheets/d/
> 1ZQ69JN1kCbkXy1VM3B02Zek_NWc3AHDexgVOA48nNDA/edit?usp=sharing
> 
> In MS Excel, the attached document created in Excel 2016 didn't even require
> some manual formatting, and displayed the values right upon formula entry in
> a clean document. The same for Excel Online, which uses latest MS code (so
> is not an issue with "not current" version 2016 used on my desktop):
> https://1drv.ms/x/s!AqRfhRdisQhQg7hBswbWoVdFancMgQ?e=AtjGw4

I am sorry and thank you very much for your time!
Anyway, as a customer working with trigonometry on Calc and on other related softwares, I think it would be easier to see the exact 0 answer for SIN(PI()) and COS(PI()/2).

Comment 5 d4mx 2020-10-26 12:42:41 UTC

(In reply to Mike Kaganski from comment #2)
> Created attachment 166704 [details]
> A sample from MS Excel 2016, from which screenshot in comment 1 was taken
> 
> (In reply to d4mx from comment #0)
> > The problem related in this report do not happen on Excel or
> > GoogleSpreadsheets.
> 
> This is wrong.
> In Google Sheets, the default format does not show the small values; but
> applying the custom number format, you may see the same values:
> https://docs.google.com/spreadsheets/d/
> 1ZQ69JN1kCbkXy1VM3B02Zek_NWc3AHDexgVOA48nNDA/edit?usp=sharing
> 
> In MS Excel, the attached document created in Excel 2016 didn't even require
> some manual formatting, and displayed the values right upon formula entry in
> a clean document. The same for Excel Online, which uses latest MS code (so
> is not an issue with "not current" version 2016 used on my desktop):
> https://1drv.ms/x/s!AqRfhRdisQhQg7hBswbWoVdFancMgQ?e=AtjGw4

Is there a way to do COS(90) direct in degrees (without conversion to rad) on Calc? Apparently when software have this option, there is no "very small number" issue.
I think (not sure) there is a way to type =COS(90) on Excel and it return exact zero. On MATLAB if you do cosd(90) it returns exact 0 (but if you type (cos(pi/2)) it returns the very small number.

Comment 6 Mike Kaganski 2020-10-26 13:34:43 UTC

(In reply to d4mx from comment #5)
> Is there a way to do COS(90) direct in degrees (without conversion to rad)
> on Calc?

As mentioned in help [1], and also in ODF standard [2], the function takes the angle in radians.

[1] https://help.libreoffice.org/7.0/en-US/text/scalc/01/04060106.html?DbPAR=CALC#bm_id3144877
[2] http://docs.oasis-open.org/office/OpenDocument/v1.3/OpenDocument-v1.3-part4-formula.html#__RefHeading__1018570_715980110