summaryrefslogtreecommitdiffstats
path: root/helpcontent2/source/text/scalc/01/func_averageifs.xhp
diff options
context:
space:
mode:
Diffstat (limited to 'helpcontent2/source/text/scalc/01/func_averageifs.xhp')
-rw-r--r--helpcontent2/source/text/scalc/01/func_averageifs.xhp74
1 files changed, 74 insertions, 0 deletions
diff --git a/helpcontent2/source/text/scalc/01/func_averageifs.xhp b/helpcontent2/source/text/scalc/01/func_averageifs.xhp
new file mode 100644
index 000000000..e94dc7bea
--- /dev/null
+++ b/helpcontent2/source/text/scalc/01/func_averageifs.xhp
@@ -0,0 +1,74 @@
+<?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_averageifsxml">
+ <title id="tit" xml-lang="en-US">AVERAGEIFS function</title>
+ <filename>/text/scalc/01/func_averageifs.xhp</filename>
+ </topic>
+</meta>
+
+<body>
+<section id="averageifs_function">
+<bookmark xml-lang="en-US" branch="hid/SC_HID_FUNC_AVERAGEIFS" id="bm_id536295362953629" localize="false"/>
+<bookmark xml-lang="en-US" branch="index" id="bm_id536715367153671">
+ <bookmark_value>AVERAGEIFS function</bookmark_value>
+ <bookmark_value>arithmetic mean;satisfying conditions</bookmark_value>
+</bookmark>
+<h2 id="hd_id537445374453744"><variable id="averageifs_head"><link href="text/scalc/01/func_averageifs.xhp">AVERAGEIFS</link></variable> function</h2>
+<paragraph id="par_id538405384053840" role="paragraph" xml-lang="en-US"><ahelp hid="."><variable id="averageifs_des">Returns the arithmetic mean of all cells in a range that satisfy given multiple criteria. The AVERAGEIFS function sums up all the results that match the logical tests and divides this sum by the quantity of selected values.</variable></ahelp></paragraph>
+</section>
+<embed href="text/scalc/00/avail_release.xhp#4.0"/>
+<embed href="text/scalc/01/common_func.xhp#sectionsyntax"/>
+<paragraph id="par_id21050267713178" role="code" xml-lang="en-US">AVERAGEIFS(<embedvar href="text/scalc/01/ex_data_stat_func.xhp#args" markup="ignore"/>)</paragraph>
+<paragraph id="par_id165832700711773" role="paragraph" xml-lang="en-US"><emph>Func_range</emph> – required argument. It is a range of cells, a name of a named range or a label of a column or a row containing values for calculating the mean.</paragraph>
+
+<embed href="text/scalc/01/ex_data_stat_func.xhp#par00"/>
+<embed href="text/shared/00/00000001.xhp#regex"/>
+<embed href="text/scalc/01/ex_data_stat_func.xhp#warning01"/>
+<embed href="text/scalc/01/ex_data_stat_func.xhp#note01"/>
+<embed href="text/scalc/01/ex_data_stat_func.xhp#par01"/>
+
+<embed href="text/scalc/01/ODFF.xhp#odff"/>
+<embed href="text/scalc/01/ex_data_stat_func.xhp#ex_func_average"/>
+<h4 id="hd_id20733192524041">Simple usage</h4>
+<paragraph id="par_id24004653627203" role="paragraph" xml-lang="en-US"><item type="input">=AVERAGEIFS(B2:B6;B2:B6;"&gt;=20")</item></paragraph>
+<paragraph id="par_id30201168686268" role="paragraph" xml-lang="en-US">Calculates the average for values of the range B2:B6 that are greater than or equal to 20. Returns 25, because the fifth row does not meet the criterion.</paragraph>
+<paragraph id="par_id30279247419921" role="paragraph" xml-lang="en-US"><item type="input">=AVERAGEIFS(C2:C6;B2:B6;"&gt;=20";C2:C6;"&gt;70")</item></paragraph>
+<paragraph id="par_id2930764965983" role="paragraph" xml-lang="en-US">Calculates the average for values of the range C2:C6 that are greater than 70 and correspond to cells of B2:B6 with values greater than or equal to 20. Returns 137.5, because the second and fifth rows do not meet at least one criterion.</paragraph>
+<h4 id="hd_id317532515726820">Using regular expressions and nested functions</h4>
+<paragraph id="par_id457966021670" role="paragraph" xml-lang="en-US"><item type="input">=AVERAGEIFS(C2:C6;B2:B6;"&gt;"&amp;MIN(B2:B6);B2:B6;"&lt;"&amp;MAX(B2:B6))</item></paragraph>
+<paragraph id="par_id66091035229950" role="paragraph" xml-lang="en-US">Calculates the average for values of the range C2:C6 that correspond to all values of the range B2:B6 except its minimum and maximum. Returns 127.5, because the third and fifth rows do not meet at least one criterion.</paragraph>
+<paragraph id="par_id303162761931870" role="paragraph" xml-lang="en-US"><item type="input">=AVERAGEIFS(C2:C6;A2:A6;"pen.*";B2:B6;"&lt;"&amp;MAX(B2:B6))</item></paragraph>
+<paragraph id="par_id40031348913642" role="paragraph" xml-lang="en-US">Calculates the average for values of the range C2:C6 that correspond to all cells of the A2:A6 range starting with "pen" and to all cells of the B2:B6 range except its maximum. Returns 65, because only second row meets all criteria.</paragraph>
+<h4 id="hd_id31201205191857">Reference to a cell as a criterion</h4>
+<paragraph id="par_id316794795433" role="paragraph" xml-lang="en-US">If you need to change a criterion easily, you may want to specify it in a separate cell and use a reference to this cell in the condition of AVERAGEIFS function. For example, the above function can be rewritten as follows:</paragraph>
+<paragraph id="par_id67531072426731" role="paragraph" xml-lang="en-US"><item type="input">=AVERAGEIFS(C2:C6;A2:A6;E2&amp;".*";B2:B6;"&lt;"&amp;MAX(B2:B6))</item></paragraph>
+<paragraph id="par_id65612244926745" role="paragraph" xml-lang="en-US">If E2 = pen, the function returns 65, because the link to the cell is substituted with its content.</paragraph>
+<embed href="text/scalc/06/calcsamplefiles.xhp#func_ifs"/>
+<section id="relatedtopics">
+ <paragraph id="par_id11921178730928" role="paragraph" xml-lang="en-US" localize="false">
+ <embedvar href="text/scalc/01/04060184.xhp#average_head"/>,
+ <embedvar href="text/scalc/01/04060184.xhp#averagea_head"/>,
+ <embedvar href="text/scalc/01/04060184.xhp#avedev_head"/>,
+ <embedvar href="text/scalc/01/func_averageif.xhp#averageif_head"/>
+ </paragraph>
+ <paragraph id="par_id11931178730928" role="paragraph" xml-lang="en-US" localize="false">
+ <embedvar href="text/scalc/01/func_countifs.xhp#countifs_head"/>,
+ <embedvar href="text/scalc/01/func_minifs.xhp#minifs_head"/>,
+ <embedvar href="text/scalc/01/func_maxifs.xhp#maxifs_head"/>,
+ <embedvar href="text/scalc/01/func_averageifs.xhp#averageifs_head"/>,
+ <embedvar href="text/scalc/01/func_sumifs.xhp#sumifs_head"/>
+ </paragraph>
+<paragraph id="par_id20741445030307" role="paragraph" localize="false" xml-lang="en-US"><embedvar href="text/shared/01/02100001.xhp#02100001"/></paragraph>
+</section>
+</body>
+</helpdocument>