AllLangHelp_scalc.mk | 1
source/auxiliary/scalc.tree | 1
source/text/scalc/01/calculation_accuracy.xhp | 108 ++++++++++++++++++++++++++
source/text/scalc/01/func_rawsubtract.xhp | 2
4 files changed, 111 insertions(+), 1 deletion(-)
New commits:
commit 69fb882589e0accf46cdaef9cc5e01cc47358137
Author: Olivier Hallot <olivier.hal...@libreoffice.org>
AuthorDate: Wed Jan 12 18:02:41 2022 -0300
Commit: Olivier Hallot <olivier.hal...@libreoffice.org>
CommitDate: Mon Jan 31 12:53:05 2022 +0100
tdf#67026 Limitation of Calc precision documented
Contents borrowed from M. Kaganski FAQ page at
https://wiki.documentfoundation.org/Faq/Calc/Accuracy
Change-Id: I89ba43c78d10ed4671b66ba0acc7df2e9947783a
Reviewed-on: https://gerrit.libreoffice.org/c/help/+/128359
Tested-by: Jenkins
Reviewed-by: Eike Rathke <er...@redhat.com>
Reviewed-by: Olivier Hallot <olivier.hal...@libreoffice.org>
diff --git a/AllLangHelp_scalc.mk b/AllLangHelp_scalc.mk
index 9c6be8ef6..fd7d20600 100644
--- a/AllLangHelp_scalc.mk
+++ b/AllLangHelp_scalc.mk
@@ -181,6 +181,7 @@ $(eval $(call gb_AllLangHelp_add_helpfiles,scalc,\
helpcontent2/source/text/scalc/01/12120300 \
helpcontent2/source/text/scalc/01/calculate \
helpcontent2/source/text/scalc/01/cell_styles \
+ helpcontent2/source/text/scalc/01/calculation_accuracy \
helpcontent2/source/text/scalc/01/data_form \
helpcontent2/source/text/scalc/01/data_provider \
helpcontent2/source/text/scalc/01/databar_more_options \
diff --git a/source/auxiliary/scalc.tree b/source/auxiliary/scalc.tree
index 849f2a82f..6106d91ab 100644
--- a/source/auxiliary/scalc.tree
+++ b/source/auxiliary/scalc.tree
@@ -23,6 +23,7 @@
<topic id="scalc/text/scalc/main0503.xhp">%PRODUCTNAME Calc
Features</topic>
<topic id="scalc/text/scalc/guide/keyboard.xhp">Shortcut Keys
(%PRODUCTNAME Calc Accessibility)</topic>
<topic id="scalc/text/scalc/04/01020000.xhp">Shortcut Keys for
Spreadsheets</topic>
+ <topic id="scalc/text/scalc/01/calculation_accuracy.xhp">Calculation
Accuracy in %PRODUCTNAME Calc</topic>
<topic id="scalc/text/scalc/05/02140000.xhp">Error Codes in %PRODUCTNAME
Calc</topic>
<topic id="scalc/text/scalc/01/04060112.xhp">Add-in for Programming in
%PRODUCTNAME Calc</topic>
<topic id="scalc/text/scalc/guide/main.xhp">Instructions for Using
%PRODUCTNAME Calc</topic>
diff --git a/source/text/scalc/01/calculation_accuracy.xhp
b/source/text/scalc/01/calculation_accuracy.xhp
new file mode 100644
index 000000000..297178db5
--- /dev/null
+++ b/source/text/scalc/01/calculation_accuracy.xhp
@@ -0,0 +1,108 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<helpdocument version="1.0">
+ <!--
+ * This file is part of the LibreOffice project.
+ *
+ * This Source Code Form is subject to the terms of the Mozilla Public
+ * License, v. 2.0. If a copy of the MPL was not distributed with this
+ * file, You can obtain one at http://mozilla.org/MPL/2.0/.
+ *
+ -->
+
+ <meta>
+ <topic id="calculation_accuracy" indexer="include" status="PUBLISH">
+ <title id="tit" xml-lang="en-US">Calculation Accuracy</title>
+ <filename>/text/scalc/01/calculation_accuracy.xhp</filename>
+ </topic>
+ </meta>
+ <body>
+ <bookmark xml-lang="en-US" branch="index" id="bm_id741642020503366">
+ <bookmark_value>calculation;accuracy</bookmark_value>
+ <bookmark_value>precision;calculation</bookmark_value>
+ </bookmark>
+ <section id="accuracyHead">
+ <h1 id="hd_id961642017927878"><link
href="text/scalc/01/calculation_accuracy.xhp" name="accuracy">%PRODUCTNAME
Calculation Accuracy</link></h1>
+ </section>
+ <h2 id="hd_id941642017968835">Inherent Accuracy Problem</h2>
+ <paragraph role="paragraph" id="par_id541642018071800">%PRODUCTNAME Calc,
just like most other spreadsheet software, uses floating-point math
capabilities available on hardware. Given that most contemporary hardware uses
binary floating-point arithmetic with limited precision defined in <link
href="https://en.wikipedia.org/wiki/IEEE_754"; name="IEEE754">IEEE 754</link>
standard, many decimal numbers - including as simple as 0.1 - cannot be
precisely represented in %PRODUCTNAME Calc (which uses 64-bit double-precision
numbers internally).</paragraph>
+ <paragraph role="paragraph" id="par_id191642019423811">Calculations with
those numbers necessarily <link
href="https://en.wikipedia.org/wiki/Floating-point_arithmetic#Accuracy_problems";
name="wpediaaccuracy">results in rounding errors</link>, and those accumulate
with every calculation. </paragraph>
+
+ <paragraph role="paragraph" id="par_id221642019437175">This is not a bug,
but is expected and currently unavoidable without using complex calculations in
software, which would incur inappropriate performance penalties, and thus is
out of question. Users need to account for that, and use rounding and
comparisons with <link href="https://en.wikipedia.org/wiki/Machine_epsilon";
name="macnie_epsilon">machine epsilon (or unit roundoff)</link> as
necessary.</paragraph>
+ <paragraph role="paragraph" id="par_id801642019531438">An example with
numbers:</paragraph>
+ <table id="tab_id261642018147815">
+ <tablerow>
+ <tablecell>
+ </tablecell>
+ <tablecell>
+ <paragraph id="par_id201642018147816" role="tablehead"
xml-lang="en-US" localize="false">A</paragraph>
+ </tablecell>
+ </tablerow>
+ <tablerow>
+ <tablecell>
+ <paragraph id="par_id801642018147817" role="tablehead"
xml-lang="en-US" localize="false">1</paragraph>
+ </tablecell>
+ <tablecell>
+ <paragraph id="par_id91642018147817" role="tablecontent"
xml-lang="en-US" localize="false">31000.99 </paragraph>
+ </tablecell>
+ </tablerow>
+ <tablerow>
+ <tablecell>
+ <paragraph id="par_id851642018218927" role="tablehead"
xml-lang="en-US" localize="false">2</paragraph>
+ </tablecell>
+ <tablecell>
+ <paragraph id="par_id831642018218928" role="tablecontent"
xml-lang="en-US" localize="false">32000.12</paragraph>
+ </tablecell>
+ </tablerow>
+ <tablerow>
+ <tablecell>
+ <paragraph id="par_id221642018243152" role="tablehead"
xml-lang="en-US" localize="false">3</paragraph>
+ </tablecell>
+ <tablecell>
+ <paragraph id="par_id261642018243152" role="tablecontent"
xml-lang="en-US" localize="false">=A1-A2</paragraph>
+ </tablecell>
+ </tablerow>
+ </table>
+ <paragraph role="paragraph" id="par_id761642018318896">This will result in
-999.129999999997 in A3, instead of expected -999.13 (you might need to
increase shown decimal places in cell format to see this).</paragraph>
+<paragraph role="paragraph" id="par_id221642020132399">An example with dates
and times:</paragraph>
+ <paragraph role="paragraph" id="par_id801642018326882">Due to the specific
of time representation in Calc, this also applies to all calculations involving
times. For example, the cells A1 and A2 below show the date and time data as
entered (in ISO 8601 format):</paragraph>
+ <table id="tab_id251642018348675">
+ <tablerow>
+ <tablecell>
+ </tablecell>
+ <tablecell>
+ <paragraph id="par_id561642018348675" role="tablehead"
xml-lang="en-US" localize="false">A</paragraph>
+ </tablecell>
+ </tablerow>
+ <tablerow>
+ <tablecell>
+ <paragraph id="par_id781642018348676" role="tablehead"
xml-lang="en-US" localize="false">1</paragraph>
+ </tablecell>
+ <tablecell>
+ <paragraph id="par_id461642018348676" role="tablecontent"
xml-lang="en-US" localize="false">2020-04-13 12:18:00</paragraph>
+ </tablecell>
+ </tablerow>
+ <tablerow>
+ <tablecell>
+ <paragraph id="par_id341642018408688" role="tablehead"
xml-lang="en-US" localize="false">2</paragraph>
+ </tablecell>
+ <tablecell>
+ <paragraph id="par_id391642018408688" role="tablecontent"
xml-lang="en-US" localize="false">2020-04-13 12:08:00 </paragraph>
+ </tablecell>
+ </tablerow>
+ <tablerow>
+ <tablecell>
+ <paragraph id="par_id401642018497660" role="tablehead"
xml-lang="en-US" localize="false">3</paragraph>
+ </tablecell>
+ <tablecell>
+ <paragraph id="par_id521642018497660" role="tablecontent"
xml-lang="en-US" localize="false">=A1-A2</paragraph>
+ </tablecell>
+ </tablerow>
+ </table>
+ <paragraph role="paragraph" id="par_id101643312991679">Cell A3 will show
00:10:00 if the default formatting [HH]:MM:SS is applied to the cell. However,
cell A3 will show 00:09:59.999999 instead of expected 00:10:00.000000 if
formatted using [HH]:MM:SS.000000 format string. This happens despite only
whole numbers of hours and minutes were used, because internally, any time is a
fraction of a day, 12:00 (noon) being represented as 0.5. </paragraph>
+ <paragraph role="paragraph" id="par_id921642020011065">The data in A1 is
represented internally as 43934.5125, and in A2 as
43934.5055555555591126903891563 (which is not exact representation of the
entered datetime, which would be 43934.505555555555555555...). </paragraph>
+ <paragraph role="paragraph" id="par_id801642020017858">Their subtraction
results in 0.00694444443287037, a value slightly less than expected
0.00694444444444..., which is 10 minutes.</paragraph>
+ <section id="relatedtopics">
+ <embed href="text/scalc/01/func_rawsubtract.xhp#rawsubtracth1"/>
+ </section>
+</body>
+</helpdocument>
diff --git a/source/text/scalc/01/func_rawsubtract.xhp
b/source/text/scalc/01/func_rawsubtract.xhp
index 55fea74db..6d59f956f 100644
--- a/source/text/scalc/01/func_rawsubtract.xhp
+++ b/source/text/scalc/01/func_rawsubtract.xhp
@@ -22,7 +22,7 @@
<bookmark_value>RAWSUBTRACT function</bookmark_value>
</bookmark>
<section id="func_rawsubtract">
- <h1 id="hd_2016112109231"><link
href="text/scalc/01/func_rawsubtract.xhp">RAWSUBTRACT</link></h1>
+ <h1 id="hd_2016112109231"><variable id="rawsubtracth1"><link
href="text/scalc/01/func_rawsubtract.xhp">RAWSUBTRACT</link></variable></h1>
<paragraph id="par_2016112109232" role="paragraph"
xml-lang="en-US"><ahelp hid=".">Subtracts a set of numbers and gives the result
without eliminating small roundoff errors.</ahelp></paragraph>
</section>
<embed href="text/scalc/01/common_func.xhp#sectionsyntax"/>
