1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
|
<?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="textscalc01func_averageifxml">
<title id="tit" xml-lang="en-US">Examples Dataset for Statistical Functions</title>
<filename>/text/scalc/01/ex_data_stat_func.xhp</filename>
</topic>
</meta>
<body>
<section id="ex_func_average">
<embed href="text/scalc/01/common_func.xhp#sectionexample"/>
<paragraph role="paragraph" id="hd_id2609201512474295">Consider the following table</paragraph>
<table id="table_averageif_example">
<tablerow>
<tablecell>
</tablecell>
<tablecell>
<paragraph id="par_id2509201517430799" role="tablehead" localize="false" xml-lang="en-US">A</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id250920151743116" role="tablehead" localize="false" xml-lang="en-US">B</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id2509201517431582" role="tablehead" localize="false" xml-lang="en-US">C</paragraph>
</tablecell>
</tablerow>
<tablerow>
<tablecell>
<paragraph id="par_id2509201517431967" role="tablehead" localize="false" xml-lang="en-US">1</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id18260631312423" role="tablecontent" xml-lang="en-US">Product Name</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id2509201517433075" role="tablecontent" xml-lang="en-US">Sales</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id2509201519383294" role="tablecontent" xml-lang="en-US">Revenue</paragraph>
</tablecell>
</tablerow>
<tablerow>
<tablecell>
<paragraph id="par_id959660094035" role="tablehead" localize="false" xml-lang="en-US">2</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id2855779586764" role="tablecontent" xml-lang="en-US">pencil</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id110202156219769" role="tablecontent" localize="false" xml-lang="en-US">20</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id23281929326512" role="tablecontent" localize="false" xml-lang="en-US">65</paragraph>
</tablecell>
</tablerow>
<tablerow>
<tablecell>
<paragraph id="par_id2509201517432278" role="tablehead" localize="false" xml-lang="en-US">3</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id24967262611733" role="tablecontent" xml-lang="en-US">pen</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id2509201517433391" role="tablecontent" localize="false" xml-lang="en-US">35</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id250920151938352" role="tablecontent" localize="false" xml-lang="en-US">85</paragraph>
</tablecell>
</tablerow>
<tablerow>
<tablecell>
<paragraph id="par_id250920151743267" role="tablehead" localize="false" xml-lang="en-US">4</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id252542413030532" role="tablecontent" xml-lang="en-US">notebook</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id2509201517433781" role="tablecontent" localize="false" xml-lang="en-US">20</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id2509201519383822" role="tablecontent" localize="false" xml-lang="en-US">190</paragraph>
</tablecell>
</tablerow>
<tablerow>
<tablecell>
<paragraph id="par_id2509201519371339" role="tablehead" localize="false" xml-lang="en-US">5</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id2509201519374677" role="tablecontent" xml-lang="en-US">book</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id2509201519373350" role="tablecontent" localize="false" xml-lang="en-US">17</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id2509201519384171" role="tablecontent" localize="false" xml-lang="en-US">180</paragraph>
</tablecell>
</tablerow>
<tablerow>
<tablecell>
<paragraph id="par_id6009596127912" role="tablehead" localize="false" xml-lang="en-US">6</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id19479417931163" role="tablecontent" xml-lang="en-US">pencil-case</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id85353130721737" role="tablecontent" xml-lang="en-US">not</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id15693941827291" role="tablecontent" xml-lang="en-US">not</paragraph>
</tablecell>
</tablerow>
</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; 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>
<section id="warning01">
<paragraph id="par_id14734320631377" role="paragraph" xml-lang="en-US"><emph>Func_Range</emph> and <emph>Range1, Range2...</emph> must have the same size, otherwise the function returns err:502 - Invalid argument.</paragraph>
</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>
<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 (<, <=, =, >, >=, <>).</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 <>, if the value is empty it matches non-empty cells.</paragraph>
<paragraph role="paragraph" id="par_id271619279215695">For <>, 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 <>, 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 <> 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 <>, 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 <> 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 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>
<paragraph id="par_id175721789527973" role="paragraph" xml-lang="en-US">If a cell contains TRUE, it is treated as 1, if a cell contains FALSE – as 0 (zero).</paragraph>
</section>
</body>
</helpdocument>
|
