|
|
| (18 intermediate revisions by one other user not shown) |
| Line 1: |
Line 1: |
| − | == Customize Keyboard ==
| + | ''' Accuracy ''' |
| − | You need an editor, which preserves Unix line ends and the coding "UTF-8", and which will not set a byte order mark. I have used "PSPAD" or "jEdit" on WinXP. On WinXP neither WordPad nor NotePad is suitable.
| + | |
| | | | |
| − | On WinXP the keybord settings for the modules are in the files
| + | 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. |
| − | <pre><..>\user\config\soffice.cfg\modules\<module name>\accelerator\<language code>\current.xml</pre>
| + | |
| − | and the general settings are in the file
| + | |
| − | <pre><..>\user\config\soffice.cfg\global\accelerator\<language code>\current.xml</pre>
| + | |
| | | | |
| − | Older versions of OOo might not have this files by default. If they are missing, goto Tool | Customize | Keyboard and change something. Then the files will be generated.
| + | == Precision in Calc == |
| | | | |
| − | # Close OOo and close quickstarter.
| + | Internally, Calc uses for its calculations floating point numbers in double precision defined in IEEE 754 standard. |
| − | # Make a copy of the file, you will work on.
| + | |
| − | # Open the file in an editor.
| + | |
| − | # Edit the file and save it.
| + | |
| − | # Start OOo.
| + | |
| | | | |
| − | If something is wrong, OOo will crash. Do not sent a crash report, it is your fault, not the fault of OOo. But restore the original file with the copy you have made in step 2.
| + | 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>. |
| | | | |
| − | To learn more about this file, see "StarOffice 7 Administration Guide" chapter 4 [http://docs-pdf.sun.com/817-1820/817-1820.pdf], "StarOFfice 8 Administration Guide" chapter 3 and appendix A [http://dlc.sun.com/pdf/817-7496/817-7496.pdf], and "OpenOffice.org XML File Format 1.0 Technical Reference Manual", chapter 9.2 [http://xml.openoffice.org/xml_specification.pdf].
| + | 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. |
| − |
| + | |
| − | Let us have a deeper look at the file. You will see something like
| + | |
| − | <pre><?xml version="1.0" encoding="UTF-8"?>
| + | |
| − | <accel:acceleratorlist xmlns:accel="http://openoffice.org/2001/accel" xmlns:xlink="http://www.w3.org/1999/xlink">
| + | |
| − | <accel:item accel:code="KEY_Q" accel:mod1="true" xlink:href=".uno:Quit"/>
| + | |
| − | <accel:item accel:code="KEY_N" accel:shift="true" accel:mod1="true" xlink:href=".uno:NewDoc"/></pre>
| + | |
| | | | |
| − | You will notice that the lines have all the same structure.
| + | ToDo: Explain setting "precision as shown". |
| | | | |
| − | <pre>accel:item</pre> A single short cut key will be defined.
| + | == Converting between Number Systems == |
| − | <pre>accel:code="KEY_Q"</pre>
| + | |
| − | You can use only keys which has got a name. I don't know where to find a list of '''all''' possible keys and which of them will work, dependent on keyboard, operating system and language.
| + | |
| | | | |
| − | Next the modifier keys are listed. You need only those, which are used, because the default value of the modifier keys is "false".
| + | Most non integer numbers have infinite decimal places in binary format, which have to be rounded somewhere. |
| − | <pre>accel:mod1="true"</pre> That is the CRTL-key
| + | 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. |
| − | <pre>accel:mod2="true"</pre> That is the ALT-key
| + | |
| − | <pre>accel:shift="true"</pre> That is the SHIFT-key
| + | |
| − | Instead of pressing the keys CRTL and Alt together, you can also press the key AltGr (That's on a German keyboard. Would be nice, if some correct this for an English keyboard).
| + | |
| | | | |
| − | The last position decribes what the shortcut should do, for example | + | 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. |
| − | <pre>xlink:href=".uno:Quit"</pre> will close OOo.
| + | |
| | | | |
| − | Inside the quotes stands a Command URL. In older OOo versions the command URL could be build as <pre>"slot:<number>"</pre> but that way should not be used for newer versions, if a ".uno" form exists.
| + | == No Symbolic π == |
| − | You find those commands in the document [http://framework.openoffice.org/files/documents/25/2570/commandsReference.html] in the first column or in the above mentioned document "StarOFfice 8 Administration Guide".
| + | |
| | | | |
| − | Some of the commands need parameters. I don't know a list of those parameters, but you can get a hint, if you record a macro and look, which parameters the dispatcher gets. For example the record of inserting a special character gives you the Command URL ".uno:InsertSymbol" and the parameter "Symbols" with for example the value "δ" (Greek delta). Or look at the existing lines in the configuration files. Perhaps you can alter a parameter?
| + | From mathematics you know <tt>sin(π)= 0</tt> and you know that <tt>tan(π/2)</tt> 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 <tt>PI()</tt> or <tt>RADIANS(180)</tt>. 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. |
| | | | |
| − | Such parameters follow the command after a ?. For inserting the character "δ" the complete Command URL is ".uno:InsertSymbol?Symbols:string=δ".
| + | {| class="wikitable" |
| − | | + | |- |
| − | Modifier keys and command URL may be exchanged.
| + | ! !! A !! B !! C |
| − | | + | |- |
| − | The complete line for inserting the special character δ when pressing the keys AltGr (German) and D together is then
| + | ! 1 |
| − | <pre><accel:item accel:code="KEY_D" accel:mod1="true" accel:mod2="true" xlink:href=".uno:InsertSymbol?Symbols:string=δ" /></pre>
| + | | 1.63317787283838E+016 || =TAN(PI()/2) || |
| − | | + | |- |
| − | == CHISQINV ==
| + | ! 2 |
| − | Calculates the inverse of the CHISQDIST function.
| + | | 1.22460635382238E-016 || =SIN(RADIANS(180)) || |
| − | | + | |- |
| − | === Syntax ===
| + | ! 3 |
| − | <tt>'''CHISQINV(p; k)'''</tt>
| + | | || || |
| − | : <tt>'''k'''</tt> is the degrees of freedom for the χ<sup>2</sup>-distribution.
| + | |} |
| − | | + | |
| − | :: Constraint: k must be a positive integer
| + | |
| − | | + | |
| − | : <tt>'''p'''</tt> is the given probability
| + | |
| − | | + | |
| − | :: Constraint: 0 ≤ p < 1
| + | |
| − | | + | |
| − | === Semantic ===
| + | |
| − | | + | |
| − | : <tt>'''CHISQINV(p; k)'''</tt> returns the value <tt>'''x'''</tt>, such that <tt>'''CHISQDIST(x; k;TRUE())''' = '''p'''</tt>.
| + | |
| − | | + | |
| − | === Example === | + | |
| − | <tt>'''CHISQINV(0.5; 9)'''</tt>
| + | |
| − | : returns approximately 8.342832692
| + | |
| − | .
| + | |
| − | | + | |
| − | === Remarks ===
| + | |
| − | If you need <tt>CHISQINV(p;k)</tt> for a non interger parameter k, then use <tt>GAMMAINV(p;k/2;2)</tt> instead.
| + | |
| − | | + | |
| − | === See also: ===
| + | |
| − | [[Documentation/How_Tos/Calc: CHISQDIST function|'''CHISQDIST''']]
| + | |
| − | [[Documentation/How_Tos/Calc: CHIDIST function|'''LEGACY.CHIDIST''']]
| + | |
| − | [[Documentation/How_Tos/Calc: CHIINV function|'''LEGACY.CHIINV''']]
| + | |
| − | | + | |
| − | [[Documentation/How_Tos/Calc: Statistical functions|'''Statistical functions''']]
| + | |
| − | | + | |
| − | [[Documentation/How_Tos/Calc: Functions listed alphabetically|'''Functions listed alphabetically''']],
| + | |
| − | [[Documentation/How_Tos/Calc: Functions listed by category|'''Functions listed by category''']]
| + | |
| − | | + | |
| − | === Issues: ===
| + | |
| − | * This function is expected for OOo3.1
| + | |
| − | | + | |
| − | | + | |
| − | == GAMMA ==
| + | |
| − | Returns the values of the Gamma function.
| + | |
| − | | + | |
| − | === Syntax ===
| + | |
| − | <tt>'''GAMMA(x)'''</tt>
| + | |
| − | | + | |
| − | :<tt>'''x'''</tt> is a number.
| + | |
| − | | + | |
| − | :: Constraint: If x is an integer, then x must be positive.
| + | |
| − | | + | |
| − | === Semantic ===
| + | |
| − | <tt>'''GAMMA(x)'''</tt> calculates
| + | |
| − | : <math>\Gamma(x) = \int_0^\infty t^{x-1} \mathrm e^{- t} \mathrm d t \,\!</math>.
| + | |
| − | | + | |
| − | | + | |
| − | | + | |
| − | === Example === | + | |
| − | : <tt>'''GAMMA(4) = 6.0'''</tt> exact
| + | |
| − | : <tt>'''GAMMA(34.56)''' ≈ 6.2336323276E+037</tt>
| + | |
| − | : <tt>'''GAMMA(−4)'''</tt> not defined
| + | |
| − | | + | |
| − | === Remarks ===
| + | |
| − | For x < 0.5 Eulers reflection formula is used.
| + | |
| − | | + | |
| − | The Gamma function has poles for negative integers and for zero. Near the poles the values are less accurate.
| + | |
| − | | + | |
| − | If x is a positive integer, then
| + | |
| − | : <math>\displaystyle \Gamma(x)=(x-1)\mathrm ! </math>
| + | |
| − | But be aware, that OpenOffice.org has only a precision of 15 digits, therefore the results for x > 21 are rounded.
| + | |
| − | | + | |
| − | === See also: ===
| + | |
| − | [[Documentation/How_Tos/Calc: GAMMADIST function|'''GAMMALN''']],
| + | |
| − | [[Documentation/How_Tos/Calc: GAMMAINV function|'''GAMMADIST''']],
| + | |
| − | [[Documentation/How_Tos/Calc: GAMMAINV function|'''GAMMAINV''']],
| + | |
| − | [[Documentation/How_Tos/Calc: GAMMAINV function|'''FACT''']]
| + | |
| − | | + | |
| − | [[Documentation/How_Tos/Calc: Mathematical functions|'''Mathematical functions''']]
| + | |
| − | | + | |
| − | [[Documentation/How_Tos/Calc: Statistical functions|'''Statistical functions''']]
| + | |
| − | | + | |
| − | [[Documentation/How_Tos/Calc: Functions listed alphabetically|'''Functions listed alphabetically''']],
| + | |
| − | [[Documentation/How_Tos/Calc: Functions listed by category|'''Functions listed by category''']]
| + | |
| − | | + | |
| − | === Issues: ===
| + | |
| − | This function is expected for OOo3.1.
| + | |
| − | | + | |
| − | | + | |
| − | == BETADIST ==
| + | |
| − | Calculates the cumulative distribution function or the probability density function of a beta distribution.
| + | |
| − | | + | |
| − | === Syntax ===
| + | |
| − | <tt>'''BETADIST(x; α; β; a; b; cumulative)'''</tt>
| + | |
| − | | + | |
| − | The beta distribution is a family of continuous probability distributions, where <tt>'''α'''</tt> and <tt>'''β'''</tt> are parameters controlling the shape of the distribution.
| + | |
| − | | + | |
| − | The parameters <tt>'''a'''</tt> and <tt>'''b'''</tt> are lower and upper bounds of the distribution. You can interpret the value a as ''location'' and the value b−a as ''scale''.
| + | |
| − | | + | |
| − | <tt>'''a'''</tt> and <tt>'''b'''</tt> are optional parameters which default (if omitted) to <tt>'''0'''</tt> and <tt>'''1'''</tt>.
| + | |
| − | | + | |
| − | <tt>'''cumulative'''</tt> is an optional parameter which defaults to TRUE() if omitted.
| + | |
| − | | + | |
| − | Before OOo version 3.1 the parameter ''cumulative'' doesn't exists and only the cumulative probability function is calculated.
| + | |
| − | | + | |
| − | Constraints:
| + | |
| − | # α > 0 , β > 0 , a < b
| + | |
| − | # If α < 1, than the density function has a pole at x = a.
| + | |
| − | # If β < 1, than the density function has a pole at x = b.
| + | |
| − | | + | |
| − | === Semantic ===
| + | |
| − | | + | |
| − | For <tt>'''cumulative = FALSE()'''</tt> the function BETADIST calculates the probability density function (besides the constraints given above):
| + | |
| − | :<math>f(x)=
| + | |
| − | \begin{cases}
| + | |
| − | 0, & \textrm{if}\; x < a \or x > b \\ | + | |
| − | \displaystyle \frac {\Gamma(\alpha + \beta)} {\Gamma(\alpha) \Gamma(\beta)} \cdot \left( \frac {x-a} {b-a} \right)^{\alpha-1}\cdot \left(1- \frac{x-a}{b-a} \right)^{\beta-1} \cdot \frac{1}{b-a}, & \textrm{if}\; a \le x \le b
| + | |
| − | \end{cases}
| + | |
| − | </math>
| + | |
| − | | + | |
| − | For <tt>'''cumulative = TRUE()'''</tt> the function BETADIST calculates the cumulative distribution function:
| + | |
| − | :<math>F(x)=
| + | |
| − | \begin{cases}
| + | |
| − | 0, & \textrm{if}\; x<a \\
| + | |
| − | 1, & \textrm{if}\; x>b \\
| + | |
| − | \displaystyle \int_a^x \frac {\Gamma(\alpha + \beta)} {\Gamma(\alpha) \Gamma(\beta)} \cdot \left( \frac {x-a} {b-a} \right)^{\alpha-1}\cdot \left(1- \frac{x-a}{b-a} \right)^{\beta-1} \mathrm{dt}, & \textrm{if}\; a \le x \le b
| + | |
| − | \end{cases}
| + | |
| − | </math>
| + | |
| − | | + | |
| − | Notice, that
| + | |
| − | :<math>\displaystyle F(x)=I_z(\alpha,\beta)</math>
| + | |
| − | where
| + | |
| − | :<math>\displaystyle z = \frac {x-a}{b-a}</math>
| + | |
| − | and <math>\scriptstyle I_z</math> is the regularized incomplete beta function.
| + | |
| − | | + | |
| − | Before version OpenOffice 3.1 the parameter <tt>'''cumulative'''</tt> doesn't exist and the function BETADIST always calculates the cumulative distribution function.
| + | |
| − | | + | |
| − | === Example: ===
| + | |
| − | <tt>'''BETADIST(0.75; 3; 4)'''</tt>
| + | |
| − | : returns approximately 0.96.
| + | |
| − | | + | |
| − | === See also: ===
| + | |
| − | [[Documentation/How_Tos/Calc: BETAINV function|'''BETAINV''']]
| + | |
| − | | + | |
| − | [[Documentation/How_Tos/Calc: Statistical functions|'''Statistical functions''']]
| + | |
| − | | + | |
| − | [[Documentation/How_Tos/Calc: Functions listed alphabetically|'''Functions listed alphabetically''']],
| + | |
| − | [[Documentation/How_Tos/Calc: Functions listed by category|'''Functions listed by category''']]
| + | |
| − | | + | |
| − | | + | |
| − | | + | |
| − | == Accuracy ==
| + | |
| | | | |
| − | === Precision in Calc === | + | == No Fractional Arithmetic == |
| | | | |
| − | 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.
| + | ToDo: Explain why 1/7 + 1/3 results in 1/2, using format code # ?/? |
| | | | |
| − | Although you can force Calc to show 15 decimal digits, these might not be all accurate. This article lists some of the problems.
| + | == Cancellation == |
| | | | |
| − | === Cancellation ===
| + | If you subtract two non integer numbers, which have nearly the same value, the result has less significant figures then the initial values. |
| | | | |
| − | 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 229: |
Line 64: |
| | | | |
| | Cell A4 shows the correct result of <math>\scriptstyle 0.999411764795882-1699/1700</math>, calculated with a computer algebra system with high precision. | | Cell A4 shows the correct result of <math>\scriptstyle 0.999411764795882-1699/1700</math>, calculated with a computer algebra system with high precision. |
| − |
| |
| − | === Converting Inaccuracy ===
| |
| − |
| |
| − | 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.
| |
| | | | |
| | {| class="wikitable" | | {| class="wikitable" |
| Line 239: |
Line 70: |
| | |- | | |- |
| | ! 1 | | ! 1 |
| − | | 0.99999876543210000000 || =0.99999876543210000000 || | + | | 0.99999876543210000000 || || |
| | |- | | |- |
| | ! 2 | | ! 2 |
| Line 251: |
Line 82: |
| | |} | | |} |
| | | | |
| − | === No Symbolic π ===
| + | Calculating manually gives 1−0.99999876543210000000 = 0.00000123456790000000 |
| | | | |
| − | From mathematics you know <tt>sin(π)= 0</tt> and you know that <tt>tan(π/2)</tt> is undefined. But you cannot get this in Calc, because the value π is always treated as rounded floating point number. It makes no difference using <tt>PI()</tt> or <tt>RADIANDS(180)</tt>. 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.
| + | |
| | + | == 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 |
| | {| class="wikitable" | | {| class="wikitable" |
| | |- | | |- |
| Line 259: |
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 |
| | | || || | | | || || |
| | |} | | |} |
| | + | |
| | + | Results shown in 15 figures precision |
| | + | {| class="wikitable" |
| | + | |- |
| | + | ! !! 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. |
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.
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.
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.
If you subtract two non integer numbers, which have nearly the same value, the result has less significant figures then the initial values.
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.
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.
, calculated with a computer algebra system with high precision.
