Difference between revisions of "User:Regina/MyDrafts"
(→Converting between Number Systems) |
|||
| Line 23: | Line 23: | ||
''A1=0,2 A2=-5 A3=A2+$A$1 runterziehen. Ab Zelle A22 ist der Fehler sichtbar.'' | ''A1=0,2 A2=-5 A3=A2+$A$1 runterziehen. Ab Zelle A22 ist der Fehler sichtbar.'' | ||
| + | |||
| + | ''Tritt auch bei A1=-5 und A2=A1+0,2 auf'' | ||
| + | |||
| + | ''Alternative A2=$A$1+((ROW()-ROW(§A$1))*2)/10. Trick: möglichst mit ganzen Zahlen rechnen und erst zum Schluss in Dezimalzahlen umrechnen.'' | ||
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. | 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. | ||
Revision as of 02:22, 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.
Contents
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.
A1=0,2 A2=-5 A3=A2+$A$1 runterziehen. Ab Zelle A22 ist der Fehler sichtbar.
Tritt auch bei A1=-5 und A2=A1+0,2 auf
Alternative A2=$A$1+((ROW()-ROW(§A$1))*2)/10. Trick: möglichst mit ganzen Zahlen rechnen und erst zum Schluss in Dezimalzahlen umrechnen.
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 Issue 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 π
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.
| A | B | C | |
|---|---|---|---|
| 1 | 0.99999876543210000000 | ||
| 2 | 0.00000123456790002141 | =1-A1 | |
| 3 | |||
| 4 |
Calculating manually gives 1−0.99999876543210000000 = 0.00000123456790000000
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?
