tdf#131995 Be precise with Criterion in Calc

+ Reused and adapted definitions of  ODF 1.2 for Criterion in
ODFF functions, as suggested in the bug description.

Change-Id: I335c6b2b9bd4236071c544cc2ad7f751db75b99f
Reviewed-on: https://gerrit.libreoffice.org/c/help/+/114613
Tested-by: Jenkins
Reviewed-by: Olivier Hallot <olivier.hallot@libreoffice.org>

5 files changed, 28 insertions, 8 deletions

diff --git a/source/text/scalc/01/04060106.xhp b/source/text/scalc/01/04060106.xhp
index cb34a41899..ae4de417cb 100644
--- a/source/text/scalc/01/04060106.xhp
+++ b/source/text/scalc/01/04060106.xhp
@@ -1054,7 +1054,7 @@
 <embed href="text/scalc/01/common_func.xhp#sectionsyntax"/>
 <paragraph id="par_id3152028" role="code" xml-lang="en-US">SUMIF(Range; Criterion [; SumRange])</paragraph>
 <paragraph id="par_id3152043" role="paragraph" xml-lang="en-US"><emph>Range</emph> is the range to which the criterion is to be applied.</paragraph>
-<paragraph id="par_id3152062" role="paragraph" xml-lang="en-US"><emph>Criterion</emph> is the cell in which the search criterion is shown, or the search criterion itself. If the criterion is written into the formula, it has to be surrounded by double quotes.</paragraph>
+<embed href="text/scalc/01/ex_data_stat_func.xhp#criterion"/>
 <paragraph id="par_id3152083" role="paragraph" xml-lang="en-US"><emph>SumRange</emph> is the range from which values are summed. If this parameter has not been indicated, the values found in the Range are summed.</paragraph>
 <note id="par_id8347422">SUMIF supports the reference concatenation operator (~) only in the Criterion parameter, and only if the optional SumRange parameter is not given.</note>
 
diff --git a/source/text/scalc/01/04060181.xhp b/source/text/scalc/01/04060181.xhp
index f32cab5e08..4ce586cb68 100644
--- a/source/text/scalc/01/04060181.xhp
+++ b/source/text/scalc/01/04060181.xhp
@@ -123,9 +123,9 @@
 <h2 id="hd_id3164897"><variable id="countif_head"><link href="text/scalc/01/04060181.xhp#countif">COUNTIF</link></variable></h2>
 <paragraph id="par_id3164926" role="paragraph" xml-lang="en-US"><ahelp hid="HID_FUNC_ZAEHLENWENN">Returns the number of cells that meet with certain criteria within a cell range.</ahelp></paragraph>
 <embed href="text/scalc/01/common_func.xhp#sectionsyntax"/>
-<paragraph id="par_id3164967" role="code" xml-lang="en-US">COUNTIF(Range; Criteria)</paragraph>
+<paragraph id="par_id3164967" role="code" xml-lang="en-US">COUNTIF(Range; Criterion)</paragraph>
 <paragraph id="par_id3164980" role="paragraph" xml-lang="en-US"><emph>Range</emph> is the range to which the criteria are to be applied.</paragraph>
-<paragraph id="par_id3165000" role="paragraph" xml-lang="en-US"><emph>Criteria</emph> indicates the criteria in the form of a number, an expression or a character string. These criteria determine which cells are counted.</paragraph>
+<embed href="text/scalc/01/ex_data_stat_func.xhp#criterion"/>
 <embed href="text/shared/00/00000001.xhp#regulaer"/>
 <embed href="text/scalc/01/common_func.xhp#sectionexample"/>
 <paragraph id="par_id3166505" role="paragraph" xml-lang="en-US">A1:A10 is a cell range containing the numbers <item type="input">2000</item> to <item type="input">2009</item>. Cell B1 contains the number <item type="input">2006</item>. In cell B2, you enter a formula:</paragraph>
diff --git a/source/text/scalc/01/ex_data_stat_func.xhp b/source/text/scalc/01/ex_data_stat_func.xhp
index 8a43857384..7981e9191d 100644
--- a/source/text/scalc/01/ex_data_stat_func.xhp
+++ b/source/text/scalc/01/ex_data_stat_func.xhp
@@ -121,7 +121,7 @@
 </table>
 <warning id="par_id1191767622119">In all examples below, ranges for calculation contain the row #6, which is ignored because it contains text.</warning>
 </section>
-<paragraph role="paragraph" id="par_id961527953004850" xml-lang="en-US"><variable id="args">Func_Range; Range1; Criterion1[; Range2; Criterion2][; … ; [Range127; Criterion127]]</variable></paragraph>
+<paragraph role="paragraph" id="par_id961527953004850" xml-lang="en-US"><variable id="args">Func_Range; Range1; Criterion[; Range2; Criterion2][; … ; [Range127; Criterion127]]</variable></paragraph>
 <section id="note01">
     <note id="par_id94162948227556">The logical relation between criteria can be defined as logical AND (conjunction). In other words, if and only if all given criteria are met, a value from the corresponding cell of the given <emph>Func_Range</emph> is taken into calculation.</note>
 </section>
@@ -130,9 +130,29 @@
 </section>
 <section id="par00">
     <paragraph id="par_id101585468691583" role="paragraph" xml-lang="en-US"><emph>Range1</emph> – required argument. It is a range of cells, a name of a named range, or a label of a column or a row, to which the corresponding criterion is to be applied.</paragraph>
-    <paragraph id="par_id23526994221948" role="paragraph" xml-lang="en-US"><emph>Criterion1</emph> – required argument. A string expression representing a logical condition or a cell reference to such string expression. The expression can contain text, numbers, regular expressions or wildcards (<link href="text/shared/optionen/01060500.xhp#wildcards" name="Wild cards">if enabled in calculation options</link>).</paragraph>
+        <section id="criterion">
+        <paragraph role="paragraph" id="par_id621619279022341"><emph>Criterion</emph>: A criterion is a single cell Reference, Number or Text. It is used in comparisons with cell contents.</paragraph>
+        <paragraph role="paragraph" id="par_id731619279029255">A reference to an empty cell is interpreted as the numeric value 0.</paragraph>
+        <paragraph role="paragraph" id="par_id31619279035359">A matching expression can be:</paragraph>
+        <list type="unordered">
+            <listitem>
+                <paragraph id="par_id171619279059462"  role="listitem">A Number or Logical value. A matching cell content equals the Number or Logical value. </paragraph>
+            </listitem>
+            <listitem>
+                <paragraph id="par_id511619279076504"  role="listitem">A value beginning with a comparator (&lt;, &lt;=, =, &gt;, &gt;=, &lt;&gt;).</paragraph>
+                <paragraph role="paragraph" id="par_id11619279168512">For =, if the value is empty it matches empty cells.</paragraph>
+                <paragraph role="paragraph" id="par_id161619279207350">For &lt;&gt;, if the value is empty it matches non-empty cells.</paragraph>
+                <paragraph role="paragraph" id="par_id271619279215695">For &lt;&gt;, if the value is not empty it matches any cell content except the value, including empty cells.</paragraph>
+                <paragraph role="paragraph" id="par_id861619279223855"><emph>Note:</emph> "=0" does not match empty cells.</paragraph>
+                <paragraph role="paragraph" id="par_id51619279363954">For = and &lt;&gt;, if the value is not empty and can not be interpreted as a Number type or one of its subtypes and the property <link href="text/shared/optionen/01060500.xhp" name="s">Search criteria = and &lt;&gt; must apply to whole cells</link> is checked, comparison is against the entire cell contents, if unchecked, comparison is against any subpart of the field that matches the criteria. For = and &lt;&gt;, if the value is not empty and can not be interpreted as a Number type or one of its subtypes applies. </paragraph>
+            </listitem>
+            <listitem>
+                <paragraph id="par_id801619279323556"  role="listitem">Other Text value. If the property <link href="text/shared/optionen/01060500.xhp" name="s">Search criteria = and &lt;&gt; must apply to whole cells</link> is true, the comparison is against the entire cell contents, if false, comparison is against any subpart of the field that matches the criteria. The expression can contain text, numbers, regular expressions or wildcards (<link href="text/shared/optionen/01060500.xhp#wildcards" name="Wild cards">if enabled in calculation options</link>).</paragraph>
+            </listitem>
+        </list>
+    </section>
     <paragraph id="par_id190621657742" role="paragraph" xml-lang="en-US"><emph>Range2</emph> – Optional. Range2 and all the following mean the same as Range1.</paragraph>
-    <paragraph id="par_id317001803813193" role="paragraph" xml-lang="en-US"><emph>Criterion2</emph> – Optional. Criterion2 and all the following mean the same as Criterion1.</paragraph>
+    <paragraph id="par_id317001803813193" role="paragraph" xml-lang="en-US"><emph>Criterion2</emph> – Optional. Criterion2 and all the following mean the same as Criterion.</paragraph>
 </section>
 <section id="par01">
     <paragraph xml-lang="en-US" id="par_id191527781733238" role="paragraph">The function can have up to 255 arguments, meaning that you can specify 127 criteria ranges and criteria for them.</paragraph>
diff --git a/source/text/scalc/01/func_averageif.xhp b/source/text/scalc/01/func_averageif.xhp
index 592afe6e75..db00a5611a 100644
--- a/source/text/scalc/01/func_averageif.xhp
+++ b/source/text/scalc/01/func_averageif.xhp
@@ -30,7 +30,7 @@
 <embed href="text/scalc/01/common_func.xhp#sectionsyntax"/>
 <paragraph id="par_id200801176228491" role="code" xml-lang="en-US">AVERAGEIF(Range; Criterion [; Average_Range ])</paragraph>
 <paragraph id="par_id15226321619829" role="paragraph" xml-lang="en-US"><emph>Range</emph> – required argument. An array, a name of named range or a label of a column or a row containing numbers for averaging or numbers or text for the condition.</paragraph>
-<paragraph id="par_id24499731228013" role="paragraph" xml-lang="en-US"><emph>Criterion</emph> – required argument. A condition in the form of expression or a cell reference with expression that defines what cells should be used to calculate the mean. The expression can contain text, numbers, regular expressions (if enabled in calculation options) or wildcards (if enabled in calculation options).</paragraph>
+<embed href="text/scalc/01/ex_data_stat_func.xhp#criterion"/>
 <embed href="text/shared/00/00000001.xhp#regulaer"/>
 <paragraph id="par_id174711913219765" role="paragraph" xml-lang="en-US"><emph>Average_Range</emph> – optional. It is a range of values for calculating the mean.</paragraph>
 <note id="par_id45123108916423">If the <emph>Average_Range</emph> is not specified, <emph>Range</emph> is used for both, the calculation of the mean and the search according to the condition. If  <emph>Average_Range</emph> is specified, the <emph>Range</emph> is used only for the condition test, while <emph>Average_Range</emph> is used for the average calculation.</note>
diff --git a/source/text/scalc/01/func_countifs.xhp b/source/text/scalc/01/func_countifs.xhp
index 2f1192b059..93c2550ebc 100644
--- a/source/text/scalc/01/func_countifs.xhp
+++ b/source/text/scalc/01/func_countifs.xhp
@@ -30,7 +30,7 @@
 <embed href="text/scalc/00/avail_release.xhp#4.0"/>
 <embed href="text/scalc/01/common_func.xhp#sectionsyntax"/>
 
-<paragraph id="par_id27421466710275" role="code" xml-lang="en-US">COUNTIFS(Range1; Criterion1[; Range2; Criterion2][; ... ; [Range127; Criterion127]])</paragraph>
+<paragraph id="par_id27421466710275" role="code" xml-lang="en-US">COUNTIFS(Range1; Criterion[; Range2; Criterion2][; ... ; [Range127; Criterion127]])</paragraph>
 <embed href="text/scalc/01/ex_data_stat_func.xhp#par00"/>
 <embed href="text/shared/00/00000001.xhp#regulaer"/>
 <paragraph role="paragraph" id="par_id14734320631377"><emph>Range1, Range2, ...</emph> and <emph>Criterion1, Criterion2, ...</emph> must have the same size, otherwise the function returns err:502 - Invalid argument.</paragraph>