Difference between revisions of "User:Regina/MyDrafts"

From Apache OpenOffice Wiki
Jump to: navigation, search
(→‎Calculating Near a Pole)
Line 1: Line 1:
−
== Accuracy ==
+
''' Accuracy '''
  
−
=== Precision in Calc ===
+
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.
  
−
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>. But because a binary format is used internally, the numbers in calculation might differ slightly from the shown decimal values. Only integers in the range <math>\scriptstyle -2^{53} \ldots 2^{53}</math> can be represented exactly in the internal format.
+
== Precision in Calc ==
  
−
Although you can force Calc to show 15 decimal digits, these might not be all accurate. This article lists some of the problems.
+
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>.
  
−
=== Cancellation ===
+
Calc rounds to two decimals in the default settings.
 +
 
 +
''Beispieltabelle''
 +
 
 +
''Hinweis auf Berechnen wie angezeigt''
 +
 
 +
 
 +
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.
 +
 
 +
== Converting between Number Systems ==
 +
 
 +
Because a binary format is used internally, the numbers in internal calculation might differ slightly from the shown decimal values.
 +
 
 +
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.
  
−
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 15: Line 27:
 
|-
 
|-
 
! 1
 
! 1
−
| 9.99411764795882E-001 || =0.999411764795882 ||
+
| 0.99999876543210000000 ||   ||
 
|-
 
|-
 
! 2
 
! 2
−
| 9.99411764705882E-001 || =1699/1700 ||   
+
| 0.00000123456790002141 || =1-A1 ||   
 
|-
 
|-
 
! 3
 
! 3
−
| 8.99997854020285E-011 || =A1-A2 ||  
+
| || ||  
 
|-
 
|-
 
! 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.
+
Calculating manually gives 1&minus;0.99999876543210000000 = 0.00000123456790000000
  
−
=== Converting Inaccuracy ===
+
Known Issue: If you fill a series by dragging the bottom right handle of the active cell, the generated values are not as accurate as they must be. Workaroung: Use Edit > fill > Series instead.
  
−
Most non integer numbers have infinite decimal places in binary format, which has to be rounded somewhere. Calculating with this rounded values and converting back to decimal format gives different values then calculating manually in decimal format.
+
== No Symbolic &pi; ==
 +
 
 +
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.
  
 
{| class="wikitable"
 
{| class="wikitable"
Line 38: Line 52:
 
|-
 
|-
 
! 1
 
! 1
−
| 0.99999876543210000000 || =0.99999876543210000000 ||
+
| 1.63317787283838E+016 || =TAN(PI()/2) ||
 
|-
 
|-
 
! 2
 
! 2
−
| 0.00000123456790002141 || =1-A1 ||   
+
| 1.22460635382238E-016 || =SIN(RADIANS(180)) ||   
 
|-
 
|-
 
! 3
 
! 3
−
|  ||  ||
 
−
|-
 
−
! 4
 
 
|  ||  ||  
 
|  ||  ||  
 
|}
 
|}
  
−
=== No Symbolic &pi; ===
+
== No Fractional Arithmetic ==
 +
 
 +
''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.
  
−
From mathematics you know <tt>sin(&pi;)= 0</tt> and you know that <tt>tan(&pi;/2)</tt> is undefined. But you cannot get this 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.
 
 
{| class="wikitable"
 
{| class="wikitable"
 
|-
 
|-
Line 58: Line 74:
 
|-
 
|-
 
! 1
 
! 1
−
| 1.63317787283838E+016 || =TAN(PI()/2) ||
+
| 9.99411764795882E-001 || =0.999411764795882 ||
 
|-
 
|-
 
! 2
 
! 2
−
| 1.22460635382238E-016 || =SIN(RADIANS(180)) ||   
+
| 9.99411764705882E-001 || =1699/1700 ||   
 
|-
 
|-
 
! 3
 
! 3
−
| ||  ||  
+
| 8.99997854020285E-011 || =A1-A2 ||
 +
|-
 +
! 4
 +
| 8.99996470588235E-011 ||  ||  
 
|}
 
|}
  
−
=== Sensitivity ===
+
Cell A4 shows the correct result of <math>\scriptstyle 0.999411764795882-1699/1700</math>, calculated with a computer algebra system with high precision.
 +
 
 +
== Ill-conditioned problems ==
 +
 
 +
''Verhalten bei Polstellen''
 +
 
 +
== Sensitivity ==
 +
 
 +
''Stabilität des Algorihtmus''
 +
 
 +
''Wie groß sind die Fehler, wenn die Eingangswerte nicht exakt sind?''
 +
 
 
How much changes the result, if the input varies with one, two, three... bit in the internal representation?
 
How much changes the result, if the input varies with one, two, three... bit in the internal representation?

Revision as of 01:37, 2 January 2009

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.

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 0.00000000000000E+000.

Calc rounds to two decimals in the default settings.

Beispieltabelle

Hinweis auf Berechnen wie angezeigt


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.

Converting between Number Systems

Because a binary format is used internally, the numbers in internal calculation might differ slightly from the shown decimal values.

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.

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

Calculating manually gives 1−0.99999876543210000000 = 0.00000123456790000000

Known Issue: If you fill a series by dragging the bottom right handle of the active cell, the generated values are not as accurate as they must be. Workaroung: Use Edit > fill > Series instead.

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 RADIANDS(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

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.

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.

Ill-conditioned problems

Verhalten bei Polstellen

Sensitivity

Stabilität des Algorihtmus

Wie groß sind die Fehler, wenn die Eingangswerte nicht exakt sind?

How much changes the result, if the input varies with one, two, three... bit in the internal representation?

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