Difference between revisions of "User:Regina/MyDrafts"

From Apache OpenOffice Wiki
Jump to: navigation, search
(→‎Converting between Number Systems)
 
(7 intermediate revisions by one other user not shown)
Line 1: Line 1:
 
''' Accuracy '''
 
''' Accuracy '''
  
−
This article is about situations, where you think, that Calc calculates wrong. You might have found a bug, and then you should write an issue. But look here first; perhaps the unexpected results come from something, you haven't been aware.
+
This article is about situations, where you think, that Calc calculates wrong. You might have found a bug, and then you should write an issue. But look here first; perhaps the unexpected results have reasons, you haven't been aware of.
  
 
== Precision in Calc ==
 
== Precision in Calc ==
  
−
Calc uses for its calculation floating point numbers in double precision as defined in IEEE 754 standard. You get the best representation in a spreadsheet cell using the scientific format with format code <tt>0.00000000000000E+000</tt>.
+
Internally, Calc uses for its calculations floating point numbers in double precision defined in IEEE 754 standard.
  
−
Calc rounds to two decimals in the default settings.
+
In default settings Calc rounds the displayed values to two decimals. You can get at most figures shown using the scientific format with format code <tt>0.00000000000000E+000</tt>.
  
−
''Beispieltabelle''
+
But although you can force Calc to show 15 decimal digits, these might not be all accurate. The following sections list some of the problems.
  
−
''Hinweis auf Berechnen wie angezeigt''
+
ToDo: Explain setting "precision as shown".
  
 +
== Converting between Number Systems ==
  
−
But although you can force Calc to show 15 decimal digits, these might not be all accurate. The following sections list some of the problems.
+
Most non integer numbers have infinite decimal places in binary format, which have to be rounded somewhere.  
 +
For example, the number 0.1 cannot be written exactly in binary format. Because of this rounding it can happen, that two mathematical different numbers have the same (rounded) internal binary value.
  
−
== Converting between Number Systems ==
+
The other way round, the decimal values are rounded to at most 15 figures. So you will see no difference between =SQRT(2) and =SQRT(2)+9E-15, but EXP(SQRT(2)) and EXP(SQRT(2)+9E-15) differs in the last shown figure.
  
−
Because a binary format is used internally, the numbers in internal calculation might differ slightly from the shown decimal values.
+
== No Symbolic &pi; ==
  
−
Most non integer numbers have infinite decimal places in binary format, which have to be rounded somewhere. Calculating with rounded values and converting back to decimal format gives different values then calculating manually in decimal format.
+
From mathematics you know <tt>sin(&pi;)= 0</tt> and you know that <tt>tan(&pi;/2)</tt> is undefined. But you cannot get these results in Calc, because the value &pi; is always treated as rounded floating point number. It makes no difference using <tt>PI()</tt> or <tt>RADIANS(180)</tt>. Calc cannot evaluate &pi; symbolically as computer algebra systems do. That is no special limitation of Calc, but other often used spreadsheet applications work only numerically, too.
 +
 
 +
{| class="wikitable"
 +
|-
 +
! !! A !! B !! C
 +
|-
 +
! 1
 +
| 1.63317787283838E+016 || =TAN(PI()/2) ||
 +
|-
 +
! 2
 +
| 1.22460635382238E-016 || =SIN(RADIANS(180)) || 
 +
|-
 +
! 3
 +
|  ||  ||
 +
|}
 +
 
 +
== No Fractional Arithmetic ==
 +
 
 +
ToDo: Explain why 1/7 + 1/3 results in 1/2, using format code # ?/?
 +
 
 +
== Cancellation ==
 +
 
 +
If you subtract two non integer numbers, which have nearly the same value, the result has less significant figures then the initial values.
 +
 
 +
{| class="wikitable"
 +
|-
 +
! !! A !! B !! C
 +
|-
 +
! 1
 +
| 9.99411764795882E-001 || =0.999411764795882 ||
 +
|-
 +
! 2
 +
| 9.99411764705882E-001 || =1699/1700 || 
 +
|-
 +
! 3
 +
| 8.99997854020285E-011 || =A1-A2 ||
 +
|-
 +
! 4
 +
| 8.99996470588235E-011 ||  ||
 +
|}
 +
 
 +
Cell A4 shows the correct result of <math>\scriptstyle 0.999411764795882-1699/1700</math>, calculated with a computer algebra system with high precision.
  
 
{| class="wikitable"
 
{| class="wikitable"
Line 41: Line 84:
 
Calculating manually gives 1&minus;0.99999876543210000000 = 0.00000123456790000000
 
Calculating manually gives 1&minus;0.99999876543210000000 = 0.00000123456790000000
  
−
Known problem: If you fill a series based on two decimal numbers by dragging the bottom right handle of the marked cells, the generated values are not as accurate as they must be, see {{Bug|88119}}. You get a better series by generating the values via Edit > Fill > Series, but still some values are not accurate in the last digit. You should control the results and correct them manually.
 
  
−
== No Symbolic &pi; ==
+
== Sensitivity ==
  
−
From mathematics you know <tt>sin(&pi;)= 0</tt> and you know that <tt>tan(&pi;/2)</tt> is undefined. But you cannot get these results in Calc, because the value &pi; is always treated as rounded floating point number. It makes no difference using <tt>PI()</tt> or <tt>RADIANDS(180)</tt>. Calc cannot evaluate &pi; symbolically as computer algebra systems do. That is no special limitation of Calc, but other often used spreadsheet applications work only numerically too.
+
Trigonometric functions are very sensitive for huge input values. That means, the results changes noticeable, if the input varies with one or two bit in the internal representation.
  
 +
Example of formula input
 
{| class="wikitable"
 
{| class="wikitable"
 
|-
 
|-
Line 52: Line 95:
 
|-
 
|-
 
! 1
 
! 1
−
| 1.63317787283838E+016 || =TAN(PI()/2) ||
+
| =2^44-0.004 || =sin(A1) ||
 
|-
 
|-
 
! 2
 
! 2
−
| 1.22460635382238E-016 || =SIN(RADIANS(180)) ||   
+
| =2^44 || =sin(A2) ||   
 
|-
 
|-
 
! 3
 
! 3
 +
| =2^44+0.004 || =sin(A3) ||
 +
|-
 +
! 4
 
|  ||  ||  
 
|  ||  ||  
 
|}
 
|}
  
−
== No Fractional Arithmetic ==
+
Results shown in 15 figures precision
−
 
+
−
''Calc kann zwar Zahlen als Brüche darstellen, aber nicht mit ihnen rechnen.''
+
−
 
+
−
== Cancellation ==
+
−
 
+
−
If you subtract two non integer numbers, which have nearly the same value, the result has less significant digits then the initial values.
+
−
 
+
 
{| class="wikitable"
 
{| class="wikitable"
 
|-
 
|-
Line 74: Line 113:
 
|-
 
|-
 
! 1
 
! 1
−
| 9.99411764795882E-001 || =0.999411764795882 ||
+
| 17592186044416.00
 +
|| 0.386569623644289
 +
||
 
|-
 
|-
 
! 2
 
! 2
−
| 9.99411764705882E-001 || =1699/1700 ||   
+
| 17592186044416.00
 +
|| 0.390169244205272
 +
||   
 
|-
 
|-
 
! 3
 
! 3
−
| 8.99997854020285E-011 || =A1-A2 ||  
+
| 17592186044416.00
 +
|| 0.393762911263629
 +
||  
 
|-
 
|-
 
! 4
 
! 4
−
| 8.99996470588235E-011 ||  ||  
+
| ||  ||  
 
|}
 
|}
  
−
Cell A4 shows the correct result of <math>\scriptstyle 0.999411764795882-1699/1700</math>, calculated with a computer algebra system with high precision.
+
The correct value rounded to 15 figures precision would be sin(2^44) = 0.390169223351877
−
 
+
−
== Ill-conditioned problems ==
+
−
 
+
−
''Verhalten bei Polstellen''
+
−
 
+
−
== Sensitivity ==
+
−
 
+
−
''Stabilität des Algorihtmus''
+
  
−
''Wie groß sind die Fehler, wenn die Eingangswerte nicht exakt sind?''
+
The trigonometric functions are reliable for integral arguments up to 2^27 with at least 13 figures accuracy and for fractional arguments up to 2^27 with at least 8 figures. For larger values the accuracy decreases to about 6 figures accuracy for integral arguments and 2 figures accuracy for fractional values reaching 2^44. Huge arguments in the area above 2^44 might be totally wrong. If you need trigonometric functions for huge arguments, a spreadsheet is the wrong application for you.
  
−
How much changes the result, if the input varies with one, two, three... bit in the internal representation?
+
The trigonometric functions are similar sensitive for arguments near their roots, but for those arguments the absolute result is near zero. So you will not notice the error, if you work with rounding to 15 or less decimal places.

Latest revision as of 07:57, 25 May 2010

Accuracy

This article is about situations, where you think, that Calc calculates wrong. You might have found a bug, and then you should write an issue. But look here first; perhaps the unexpected results have reasons, you haven't been aware of.

Precision in Calc

Internally, Calc uses for its calculations floating point numbers in double precision defined in IEEE 754 standard.

In default settings Calc rounds the displayed values to two decimals. You can get at most figures shown using the scientific format with format code 0.00000000000000E+000.

But although you can force Calc to show 15 decimal digits, these might not be all accurate. The following sections list some of the problems.

ToDo: Explain setting "precision as shown".

Converting between Number Systems

Most non integer numbers have infinite decimal places in binary format, which have to be rounded somewhere. For example, the number 0.1 cannot be written exactly in binary format. Because of this rounding it can happen, that two mathematical different numbers have the same (rounded) internal binary value.

The other way round, the decimal values are rounded to at most 15 figures. So you will see no difference between =SQRT(2) and =SQRT(2)+9E-15, but EXP(SQRT(2)) and EXP(SQRT(2)+9E-15) differs in the last shown figure.

No Symbolic π

From mathematics you know sin(π)= 0 and you know that tan(π/2) is undefined. But you cannot get these results in Calc, because the value π is always treated as rounded floating point number. It makes no difference using PI() or RADIANS(180). Calc cannot evaluate π symbolically as computer algebra systems do. That is no special limitation of Calc, but other often used spreadsheet applications work only numerically, too.

A B C
1 1.63317787283838E+016 =TAN(PI()/2)
2 1.22460635382238E-016 =SIN(RADIANS(180))
3

No Fractional Arithmetic

ToDo: Explain why 1/7 + 1/3 results in 1/2, using format code # ?/?

Cancellation

If you subtract two non integer numbers, which have nearly the same value, the result has less significant figures then the initial values.

A B C
1 9.99411764795882E-001 =0.999411764795882
2 9.99411764705882E-001 =1699/1700
3 8.99997854020285E-011 =A1-A2
4 8.99996470588235E-011

Cell A4 shows the correct result of , calculated with a computer algebra system with high precision.

A B C
1 0.99999876543210000000
2 0.00000123456790002141 =1-A1
3
4

Calculating manually gives 1−0.99999876543210000000 = 0.00000123456790000000


Sensitivity

Trigonometric functions are very sensitive for huge input values. That means, the results changes noticeable, if the input varies with one or two bit in the internal representation.

Example of formula input

A B C
1 =2^44-0.004 =sin(A1)
2 =2^44 =sin(A2)
3 =2^44+0.004 =sin(A3)
4

Results shown in 15 figures precision

A B C
1 17592186044416.00 0.386569623644289
2 17592186044416.00 0.390169244205272
3 17592186044416.00 0.393762911263629
4

The correct value rounded to 15 figures precision would be sin(2^44) = 0.390169223351877

The trigonometric functions are reliable for integral arguments up to 2^27 with at least 13 figures accuracy and for fractional arguments up to 2^27 with at least 8 figures. For larger values the accuracy decreases to about 6 figures accuracy for integral arguments and 2 figures accuracy for fractional values reaching 2^44. Huge arguments in the area above 2^44 might be totally wrong. If you need trigonometric functions for huge arguments, a spreadsheet is the wrong application for you.

The trigonometric functions are similar sensitive for arguments near their roots, but for those arguments the absolute result is near zero. So you will not notice the error, if you work with rounding to 15 or less decimal places.

Retrieved from "https://wiki.openoffice.org/w/index.php?title=User:Regina/MyDrafts&oldid=169190"
Personal tools