Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenFri, 08 Jun 2018 15:51:24 +0200Sum of a row of a named rangehttps://ask.libreoffice.org/en/question/157201/sum-of-a-row-of-a-named-range/In LibreOffice Calc, how can I sum a row of a named range?
Assume I have a named range, `foo`, that looks like this:
1 2 3
4 5 6
7 8 9
I'd like to add another column with a sum each row in `foo`, like this:
1 2 3 | 6
4 5 6 | 15
7 8 9 | 24
I am looking for a way to do this only by referring to `foo`, and not directly to the cell locations.Wed, 06 Jun 2018 21:33:49 +0200https://ask.libreoffice.org/en/question/157201/sum-of-a-row-of-a-named-range/Answer by pierre-yves samyn for <p>In LibreOffice Calc, how can I sum a row of a named range?</p>
<p>Assume I have a named range, <code>foo</code>, that looks like this:</p>
<pre><code>1 2 3
4 5 6
7 8 9
</code></pre>
<p>I'd like to add another column with a sum each row in <code>foo</code>, like this:</p>
<pre><code>1 2 3 | 6
4 5 6 | 15
7 8 9 | 24
</code></pre>
<p>I am looking for a way to do this only by referring to <code>foo</code>, and not directly to the cell locations.</p>
https://ask.libreoffice.org/en/question/157201/sum-of-a-row-of-a-named-range/?answer=157262#post-id-157262Hi
For example:
=SUMPRODUCT(INDEX(foo;ROW(A1);COLUMN($A$1:$C$1)))
Note:
- Here `A1` and `$A$1:$C$1` are not the cell addresses. *ROW* and *COLUMN* functions generate *numbers* (e.g. 1 to 3 for `COLUMN($A$1:$C$1)`
- So adapt COLUMN to the actual number of columns in your range.
[SumRowRange.ods](/upfiles/15283493992677012.ods)
HTH - RegardsThu, 07 Jun 2018 07:27:37 +0200https://ask.libreoffice.org/en/question/157201/sum-of-a-row-of-a-named-range/?answer=157262#post-id-157262Comment by JohnSUN for <p>Hi</p>
<p>For example:</p>
<pre><code>=SUMPRODUCT(INDEX(foo;ROW(A1);COLUMN($A$1:$C$1)))
</code></pre>
<p>Note: </p>
<ul>
<li>Here <code>A1</code> and <code>$A$1:$C$1</code> are not the cell addresses. <em>ROW</em> and <em>COLUMN</em> functions generate <em>numbers</em> (e.g. 1 to 3 for <code>COLUMN($A$1:$C$1)</code></li>
<li>So adapt COLUMN to the actual number of columns in your range.</li>
</ul>
<p><a href="/upfiles/15283493992677012.ods">SumRowRange.ods</a></p>
<p>HTH - Regards</p>
https://ask.libreoffice.org/en/question/157201/sum-of-a-row-of-a-named-range/?comment=157264#post-id-157264We can again use ROW in the third parameter (instead of COLUMN) - `=SUMPRODUCT(INDEX(foo;ROW(A1); ROW($A$1:$A$3)))` :-)Thu, 07 Jun 2018 07:37:31 +0200https://ask.libreoffice.org/en/question/157201/sum-of-a-row-of-a-named-range/?comment=157264#post-id-157264Comment by pierre-yves samyn for <p>Hi</p>
<p>For example:</p>
<pre><code>=SUMPRODUCT(INDEX(foo;ROW(A1);COLUMN($A$1:$C$1)))
</code></pre>
<p>Note: </p>
<ul>
<li>Here <code>A1</code> and <code>$A$1:$C$1</code> are not the cell addresses. <em>ROW</em> and <em>COLUMN</em> functions generate <em>numbers</em> (e.g. 1 to 3 for <code>COLUMN($A$1:$C$1)</code></li>
<li>So adapt COLUMN to the actual number of columns in your range.</li>
</ul>
<p><a href="/upfiles/15283493992677012.ods">SumRowRange.ods</a></p>
<p>HTH - Regards</p>
https://ask.libreoffice.org/en/question/157201/sum-of-a-row-of-a-named-range/?comment=157272#post-id-157272Hi @JohnSUN
Yes, of course, since it is only a matter of generating numbers. It's just an "educational" choice to not add difficulties of understanding. That said ... it may also cause confusion, who knows :)
RegardsThu, 07 Jun 2018 08:57:20 +0200https://ask.libreoffice.org/en/question/157201/sum-of-a-row-of-a-named-range/?comment=157272#post-id-157272Comment by hjek for <p>Hi</p>
<p>For example:</p>
<pre><code>=SUMPRODUCT(INDEX(foo;ROW(A1);COLUMN($A$1:$C$1)))
</code></pre>
<p>Note: </p>
<ul>
<li>Here <code>A1</code> and <code>$A$1:$C$1</code> are not the cell addresses. <em>ROW</em> and <em>COLUMN</em> functions generate <em>numbers</em> (e.g. 1 to 3 for <code>COLUMN($A$1:$C$1)</code></li>
<li>So adapt COLUMN to the actual number of columns in your range.</li>
</ul>
<p><a href="/upfiles/15283493992677012.ods">SumRowRange.ods</a></p>
<p>HTH - Regards</p>
https://ask.libreoffice.org/en/question/157201/sum-of-a-row-of-a-named-range/?comment=157305#post-id-157305Thanks for answering so quickly.
I'm not sure I understand the idea in this answer as `COLUMN($A$1:$C$1)` still clearly refers directly to an unnamed cell range, which exactly what I'm trying *not* to do. If I wanted to refer directly to an unnamed cell range, the *whole* formula would just be `=SUM(A1:C1)`, right?Thu, 07 Jun 2018 12:31:10 +0200https://ask.libreoffice.org/en/question/157201/sum-of-a-row-of-a-named-range/?comment=157305#post-id-157305Comment by pierre-yves samyn for <p>Hi</p>
<p>For example:</p>
<pre><code>=SUMPRODUCT(INDEX(foo;ROW(A1);COLUMN($A$1:$C$1)))
</code></pre>
<p>Note: </p>
<ul>
<li>Here <code>A1</code> and <code>$A$1:$C$1</code> are not the cell addresses. <em>ROW</em> and <em>COLUMN</em> functions generate <em>numbers</em> (e.g. 1 to 3 for <code>COLUMN($A$1:$C$1)</code></li>
<li>So adapt COLUMN to the actual number of columns in your range.</li>
</ul>
<p><a href="/upfiles/15283493992677012.ods">SumRowRange.ods</a></p>
<p>HTH - Regards</p>
https://ask.libreoffice.org/en/question/157201/sum-of-a-row-of-a-named-range/?comment=157323#post-id-157323@hjek - I had tried to anticipate this question by writing my first note. The ROW and COLUMN functions are *only used here to generate numbers*.
But no matter because @Lupp's answer is better :)Thu, 07 Jun 2018 15:15:46 +0200https://ask.libreoffice.org/en/question/157201/sum-of-a-row-of-a-named-range/?comment=157323#post-id-157323Answer by Lupp for <p>In LibreOffice Calc, how can I sum a row of a named range?</p>
<p>Assume I have a named range, <code>foo</code>, that looks like this:</p>
<pre><code>1 2 3
4 5 6
7 8 9
</code></pre>
<p>I'd like to add another column with a sum each row in <code>foo</code>, like this:</p>
<pre><code>1 2 3 | 6
4 5 6 | 15
7 8 9 | 24
</code></pre>
<p>I am looking for a way to do this only by referring to <code>foo</code>, and not directly to the cell locations.</p>
https://ask.libreoffice.org/en/question/157201/sum-of-a-row-of-a-named-range/?answer=157277#post-id-157277`<kidding>`As a **retired** teacher I may know too little about educational choices. But...`</kidding>`
I would like to also remind you of the fact that the INDEX() function allows for selecting complete (intersected) rows or columns from a range (whether referenced via direct addressing or via a defined name) by **incomplete indexing**. See **[this](/upfiles/15283591867032762.ods)** ornate demo.
(See also 6.14.6 of `http://docs.oasis-open.org/office/v1.2/OpenDocument-v1.2-part2.odt`.)Thu, 07 Jun 2018 10:23:02 +0200https://ask.libreoffice.org/en/question/157201/sum-of-a-row-of-a-named-range/?answer=157277#post-id-157277Comment by hjek for <p><code><kidding></code>As a <strong>retired</strong> teacher I may know too little about educational choices. But...<code></kidding></code> </p>
<p>I would like to also remind you of the fact that the INDEX() function allows for selecting complete (intersected) rows or columns from a range (whether referenced via direct addressing or via a defined name) by <strong>incomplete indexing</strong>. See <strong><a href="/upfiles/15283591867032762.ods">this</a></strong> ornate demo. </p>
<p>(See also 6.14.6 of <code>http://docs.oasis-open.org/office/v1.2/OpenDocument-v1.2-part2.odt</code>.)</p>
https://ask.libreoffice.org/en/question/157201/sum-of-a-row-of-a-named-range/?comment=157310#post-id-157310Nice. This is what I'm looking for. In particular the **Only row index** formula, but just changing the hardcoded row number to `ROW()`. Se the answer is:
=SUM(INDEX(foo;ROW();))Thu, 07 Jun 2018 12:35:41 +0200https://ask.libreoffice.org/en/question/157201/sum-of-a-row-of-a-named-range/?comment=157310#post-id-157310Comment by Lupp for <p><code><kidding></code>As a <strong>retired</strong> teacher I may know too little about educational choices. But...<code></kidding></code> </p>
<p>I would like to also remind you of the fact that the INDEX() function allows for selecting complete (intersected) rows or columns from a range (whether referenced via direct addressing or via a defined name) by <strong>incomplete indexing</strong>. See <strong><a href="/upfiles/15283591867032762.ods">this</a></strong> ornate demo. </p>
<p>(See also 6.14.6 of <code>http://docs.oasis-open.org/office/v1.2/OpenDocument-v1.2-part2.odt</code>.)</p>
https://ask.libreoffice.org/en/question/157201/sum-of-a-row-of-a-named-range/?comment=157312#post-id-157312What answer?
Anyway: Using the `ROW()` function without an argument returns the absolute row-number of the cell the formula is contained in. This may not be the **relative** number of the rows occurring in your range you want to address. `INDEX()` starts with row-number 1 for the first row **of your range** wherever that is in the sheet.
(I probably misunderstood you basically.)Thu, 07 Jun 2018 12:49:38 +0200https://ask.libreoffice.org/en/question/157201/sum-of-a-row-of-a-named-range/?comment=157312#post-id-157312Comment by hjek for <p><code><kidding></code>As a <strong>retired</strong> teacher I may know too little about educational choices. But...<code></kidding></code> </p>
<p>I would like to also remind you of the fact that the INDEX() function allows for selecting complete (intersected) rows or columns from a range (whether referenced via direct addressing or via a defined name) by <strong>incomplete indexing</strong>. See <strong><a href="/upfiles/15283591867032762.ods">this</a></strong> ornate demo. </p>
<p>(See also 6.14.6 of <code>http://docs.oasis-open.org/office/v1.2/OpenDocument-v1.2-part2.odt</code>.)</p>
https://ask.libreoffice.org/en/question/157201/sum-of-a-row-of-a-named-range/?comment=157327#post-id-157327Well, the absolute row-number of the cell formula is OK, because in the question I asked the cell formula is right next to the row it is summing.
Your answer was good enough for me to get it, even if you happened to misunderstand me a little bit.Thu, 07 Jun 2018 15:43:29 +0200https://ask.libreoffice.org/en/question/157201/sum-of-a-row-of-a-named-range/?comment=157327#post-id-157327Comment by Lupp for <p><code><kidding></code>As a <strong>retired</strong> teacher I may know too little about educational choices. But...<code></kidding></code> </p>
<p>I would like to also remind you of the fact that the INDEX() function allows for selecting complete (intersected) rows or columns from a range (whether referenced via direct addressing or via a defined name) by <strong>incomplete indexing</strong>. See <strong><a href="/upfiles/15283591867032762.ods">this</a></strong> ornate demo. </p>
<p>(See also 6.14.6 of <code>http://docs.oasis-open.org/office/v1.2/OpenDocument-v1.2-part2.odt</code>.)</p>
https://ask.libreoffice.org/en/question/157201/sum-of-a-row-of-a-named-range/?comment=157362#post-id-157362Quoting @hjek: "...because in the question I asked the cell formula is right next to the row it is summing."
This was exacly the reason for which I doubted if the logical difference between the ROW() of the sheet and the row of the range was regarded. Though it is not relevant as long as the named range starts in the first row of its sheet, it may be relevant tomorrow when, e.g, a row was inserted above the range to place headings there.Thu, 07 Jun 2018 21:13:03 +0200https://ask.libreoffice.org/en/question/157201/sum-of-a-row-of-a-named-range/?comment=157362#post-id-157362Comment by hjek for <p><code><kidding></code>As a <strong>retired</strong> teacher I may know too little about educational choices. But...<code></kidding></code> </p>
<p>I would like to also remind you of the fact that the INDEX() function allows for selecting complete (intersected) rows or columns from a range (whether referenced via direct addressing or via a defined name) by <strong>incomplete indexing</strong>. See <strong><a href="/upfiles/15283591867032762.ods">this</a></strong> ornate demo. </p>
<p>(See also 6.14.6 of <code>http://docs.oasis-open.org/office/v1.2/OpenDocument-v1.2-part2.odt</code>.)</p>
https://ask.libreoffice.org/en/question/157201/sum-of-a-row-of-a-named-range/?comment=157411#post-id-157411@Lupp: Ok, I see the problem now when inserting rows above the named range (but I didn't run into that problem because my named range included the *entire* columns, which I should probably have been explicit about in the question.)Fri, 08 Jun 2018 13:29:07 +0200https://ask.libreoffice.org/en/question/157201/sum-of-a-row-of-a-named-range/?comment=157411#post-id-157411Comment by hjek for <p><code><kidding></code>As a <strong>retired</strong> teacher I may know too little about educational choices. But...<code></kidding></code> </p>
<p>I would like to also remind you of the fact that the INDEX() function allows for selecting complete (intersected) rows or columns from a range (whether referenced via direct addressing or via a defined name) by <strong>incomplete indexing</strong>. See <strong><a href="/upfiles/15283591867032762.ods">this</a></strong> ornate demo. </p>
<p>(See also 6.14.6 of <code>http://docs.oasis-open.org/office/v1.2/OpenDocument-v1.2-part2.odt</code>.)</p>
https://ask.libreoffice.org/en/question/157201/sum-of-a-row-of-a-named-range/?comment=157425#post-id-157425True.
Yet the spreadsheet I'm currently working has more complicated formulae, so named ranges are useful for readability (or at least [Joel Spolsky](https://www.youtube.com/watch?v=0nbkaYsR94c) has convinced me of that). I was just trying to ask a more clear and general question, rather than one peppered with unnecessary complecting specificity.Fri, 08 Jun 2018 15:14:14 +0200https://ask.libreoffice.org/en/question/157201/sum-of-a-row-of-a-named-range/?comment=157425#post-id-157425Comment by Lupp for <p><code><kidding></code>As a <strong>retired</strong> teacher I may know too little about educational choices. But...<code></kidding></code> </p>
<p>I would like to also remind you of the fact that the INDEX() function allows for selecting complete (intersected) rows or columns from a range (whether referenced via direct addressing or via a defined name) by <strong>incomplete indexing</strong>. See <strong><a href="/upfiles/15283591867032762.ods">this</a></strong> ornate demo. </p>
<p>(See also 6.14.6 of <code>http://docs.oasis-open.org/office/v1.2/OpenDocument-v1.2-part2.odt</code>.)</p>
https://ask.libreoffice.org/en/question/157201/sum-of-a-row-of-a-named-range/?comment=157420#post-id-157420Assuming everything was clear and explicit:
In such a case I would surely prefer to use `=SUM()` with direct addressing instead of using a named range. This would read `=SUM($A1:$C1)` in the first row, and filling the formula down it would adapt automatically its (relative) row part of the range address.Fri, 08 Jun 2018 14:47:06 +0200https://ask.libreoffice.org/en/question/157201/sum-of-a-row-of-a-named-range/?comment=157420#post-id-157420Comment by Lupp for <p><code><kidding></code>As a <strong>retired</strong> teacher I may know too little about educational choices. But...<code></kidding></code> </p>
<p>I would like to also remind you of the fact that the INDEX() function allows for selecting complete (intersected) rows or columns from a range (whether referenced via direct addressing or via a defined name) by <strong>incomplete indexing</strong>. See <strong><a href="/upfiles/15283591867032762.ods">this</a></strong> ornate demo. </p>
<p>(See also 6.14.6 of <code>http://docs.oasis-open.org/office/v1.2/OpenDocument-v1.2-part2.odt</code>.)</p>
https://ask.libreoffice.org/en/question/157201/sum-of-a-row-of-a-named-range/?comment=157427#post-id-157427Well, I never was patient enough to watch youtube for that kind of learning. From my own experience I would judge that named ranges can be useful, but actually are in rather rare cases. You will surely collect your own experiences...
However, where I have the actual data and the formulae in such a close relation (concerning the position) I would not expect much explanatory power in using a name. To define and use the name in different formulae elsewhere is possible anyway.Fri, 08 Jun 2018 15:51:24 +0200https://ask.libreoffice.org/en/question/157201/sum-of-a-row-of-a-named-range/?comment=157427#post-id-157427