source/text/scalc/01/04060107.xhp | 43 +++++++++++++++++++++++++++++---------
1 file changed, 33 insertions(+), 10 deletions(-)
New commits:
commit d26dc8bf2215097db8092428cc05a991aa03f8a4
Author: Olivier Hallot <olivier.hal...@libreoffice.org>
AuthorDate: Fri Nov 11 18:10:47 2022 -0300
Commit: Olivier Hallot <olivier.hal...@libreoffice.org>
CommitDate: Sat Nov 12 11:41:18 2022 +0100
tdf#89358 Example of implicit intersection for array formulas
Change-Id: I6cb1b66a34f8bd95472e78dac03f8860c2cdd375
Reviewed-on: https://gerrit.libreoffice.org/c/help/+/142605
Tested-by: Jenkins
Reviewed-by: Olivier Hallot <olivier.hal...@libreoffice.org>
diff --git a/source/text/scalc/01/04060107.xhp
b/source/text/scalc/01/04060107.xhp
index 86514c4116..dad50459e3 100644
--- a/source/text/scalc/01/04060107.xhp
+++ b/source/text/scalc/01/04060107.xhp
@@ -60,19 +60,26 @@
<tablecell>
<paragraph id="par_id3155325" role="tablehead"
localize="false">C</paragraph>
</tablecell>
+ <tablecell>
+ <paragraph id="par_id31668198570666" role="tablehead"
localize="false">D</paragraph>
+ </tablecell>
+ <tablecell>
+ <paragraph id="par_id441668198589386" role="tablehead"
localize="false">E</paragraph>
+ </tablecell>
+
</tablerow>
<tablerow>
<tablecell>
<paragraph id="par_id3153104" role="tablehead"
localize="false">1</paragraph>
</tablecell>
<tablecell>
- <paragraph id="par_id3146996" role="paragraph" localize="false"><item
type="input">7</item></paragraph>
+ <paragraph id="par_id3146996" role="paragraph"
localize="false"><literal>7</literal></paragraph>
</tablecell>
<tablecell>
- <paragraph id="par_id3150529" role="paragraph" localize="false"><item
type="input">31</item></paragraph>
+ <paragraph id="par_id3150529" role="paragraph"
localize="false"><literal>31</literal></paragraph>
</tablecell>
<tablecell>
- <paragraph id="par_id3148831" role="paragraph" localize="false"><item
type="input">33</item></paragraph>
+ <paragraph id="par_id3148831" role="paragraph"
localize="false"><literal>33</literal></paragraph>
</tablecell>
</tablerow>
<tablerow>
@@ -80,13 +87,13 @@
<paragraph id="par_id3148943" role="tablehead"
localize="false">2</paragraph>
</tablecell>
<tablecell>
- <paragraph id="par_id3149771" role="paragraph" localize="false"><item
type="input">95</item></paragraph>
+ <paragraph id="par_id3149771" role="paragraph"
localize="false"><literal>95</literal></paragraph>
</tablecell>
<tablecell>
- <paragraph id="par_id3158407" role="paragraph" localize="false"><item
type="input">17</item></paragraph>
+ <paragraph id="par_id3158407" role="paragraph"
localize="false"><literal>17</literal></paragraph>
</tablecell>
<tablecell>
- <paragraph id="par_id3148806" role="paragraph" localize="false"><item
type="input">2</item></paragraph>
+ <paragraph id="par_id3148806" role="paragraph"
localize="false"><literal>2</literal></paragraph>
</tablecell>
</tablerow>
<tablerow>
@@ -94,13 +101,18 @@
<paragraph id="par_id3154904" role="tablehead"
localize="false">3</paragraph>
</tablecell>
<tablecell>
- <paragraph id="par_id3150779" role="paragraph" localize="false"><item
type="input">5</item></paragraph>
+ <paragraph id="par_id3150779" role="paragraph"
localize="false"><literal>5</literal></paragraph>
</tablecell>
<tablecell>
- <paragraph id="par_id3148449" role="paragraph" localize="false"><item
type="input">10</item></paragraph>
+ <paragraph id="par_id3148449" role="paragraph"
localize="false"><literal>10</literal></paragraph>
</tablecell>
<tablecell>
- <paragraph id="par_id3147238" role="paragraph" localize="false"><item
type="input">50</item></paragraph>
+ <paragraph id="par_id3147238" role="paragraph"
localize="false"><literal>50</literal></paragraph>
+ </tablecell>
+ </tablerow>
+ <tablerow>
+ <tablecell>
+ <paragraph id="par_id321668199240111" role="tablehead"
localize="false">4</paragraph>
</tablecell>
</tablerow>
</table>
@@ -115,7 +127,18 @@
<paragraph id="par_id3149787" role="paragraph" xml-lang="en-US">Use array
formulas if you have to repeat calculations using different values. If you
decide to change the calculation method later, you only have to update the
array formula. To add an array formula, select the entire array range and then
<link href="text/scalc/01/04060107.xhp" name="make the required change to the
array formula">make the required change to the array formula</link>.</paragraph>
<paragraph id="par_id3149798" role="paragraph" xml-lang="en-US">Array
formulas are also a space saving option when several values must be calculated,
since they are not very memory-intensive. In addition, arrays are an essential
tool for carrying out complex calculations, because you can have several cell
ranges included in your calculations. $[officename] has different math
functions for arrays, such as the MMULT function for multiplying two arrays or
the SUMPRODUCT function for calculating the scalar products of two
arrays.</paragraph>
<h2 id="hd_id3155588">Using Array Formulas in $[officename] Calc</h2>
- <paragraph id="par_id3152876" role="paragraph" xml-lang="en-US">You can also
create a "normal" formula in which the reference range, such as parameters,
indicate an array formula. The result is obtained from the intersection of the
reference range and the rows or columns in which the formula is found. If there
is no intersection or if the range at the intersection covers several rows or
columns, a #VALUE! error message appears. The following example illustrates
this concept:</paragraph>
+<h3 id="hd_id651668200191409">Implicit intersection of array formulas</h3>
+ <paragraph id="par_id3152876" role="paragraph" xml-lang="en-US">You can also
create a "normal" formula in which the reference range, such as parameters,
indicate an array formula. This formula is also known as "implicit
intersection" of array formula. The result is obtained from the intersection of
the reference range and the rows or columns in which the formula is found. If
there is no intersection or if the range at the intersection covers several
rows or columns, a #VALUE! error message appears. The following example
illustrates this concept:</paragraph>
+ <paragraph role="paragraph" id="par_id631668200233375">In the table above,
place the array formula in D1:</paragraph>
+ <paragraph role="paragraph" id="par_id691668200248323"
localize="false"><input>{=A1:A3 + 100}</input></paragraph>
+ <paragraph role="paragraph" id="par_id641668200266239">Cells D1, D2, D3 have
values 107, 195, 105 respectively.</paragraph>
+ <paragraph role="paragraph" id="par_id231668200272420">Insert the formula
below in E2, do not enter as array formula.</paragraph>
+ <paragraph role="paragraph" id="par_id361668200276892"
localize="false"><input>=A1:A3 + 100</input></paragraph>
+ <paragraph role="paragraph" id="par_id681668200283910">Cells E1 and E3 are
empty, Cell E2 has value 195. This is the implicit intersection of array
formulas.</paragraph>
+ <paragraph role="paragraph" id="par_id161668200288651">Insert formula below
in E4, as in E2.</paragraph>
+ <paragraph role="paragraph" id="par_id471668200292845"
localize="false"><input>=A1:A3 + 100</input></paragraph>
+ <paragraph role="paragraph" id="par_id711668200299613">Cell E4 display
#VALUE!. Row 4 is out of the range A1:A3 of the formula.</paragraph>
+
<h3 id="hd_id3151271">Creating Array Formulas</h3>
<section id="creating_array_formulas">
<paragraph id="par_id3149102" role="paragraph" xml-lang="en-US">If you
create an array formula using the <emph>Function Wizard</emph>, you must mark
the <emph>Array</emph> check box each time so that the results are returned in
an array. Otherwise, only the value in the upper-left cell of the array being
calculated is returned.</paragraph>
