Is there a way to simplify summing Writer table cells?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Is there a way to simplify summing Writer table cells?

Girvin Herr-2
Greetings,

I have a Writer document with about 50 rows in a table. At the bottom of
the table, I want to sum the above column of cells in each row (a
total). Currently, the only way to add a new row or rows to this sum is
to add the cell reference(s) (i.e. |<J47>) to the long string of cell
references to sum. Is this the only way, other than dragging from the
first cell to the last cell?

I tried =sum(J2:Jn) and even =sum(<J2>:<Jn>) but that just displays a
formula error message in the sum cell.

I would like to be able to add rows to this table as needed and have an
easy way to add the new cells to the sum. A way of specifying a range of
cells is perfect. As I now do it, the more rows I need to add, the more
tedious and error-prone this process becomes.

Thanks.

Girvin Herr



---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Is there a way to simplify summing Writer table cells?

Steven Ahlers
Givin,

Have you tried a "," or a ";" instead of a ":" ?

Sent from my iPhone

> On Jul 13, 2017, at 3:11 PM, Girvin Herr <[hidden email]> wrote:
>
> Greetings,
>
> I have a Writer document with about 50 rows in a table. At the bottom of the table, I want to sum the above column of cells in each row (a total). Currently, the only way to add a new row or rows to this sum is to add the cell reference(s) (i.e. |<J47>) to the long string of cell references to sum. Is this the only way, other than dragging from the first cell to the last cell?
>
> I tried =sum(J2:Jn) and even =sum(<J2>:<Jn>) but that just displays a formula error message in the sum cell.
>
> I would like to be able to add rows to this table as needed and have an easy way to add the new cells to the sum. A way of specifying a range of cells is perfect. As I now do it, the more rows I need to add, the more tedious and error-prone this process becomes.
>
> Thanks.
>
> Girvin Herr
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Is there a way to simplify summing Writer table cells?

Girvin Herr-2
Steven,

I just tried both, and with and without the <> and got the same results:
**Expression is faulty **.

Thanks for the help.
Girvin



On 07/13/2017 03:47 PM, Steven Ahlers wrote:

> Givin,
>
> Have you tried a "," or a ";" instead of a ":" ?
>
> Sent from my iPhone
>
>> On Jul 13, 2017, at 3:11 PM, Girvin Herr <[hidden email]> wrote:
>>
>> Greetings,
>>
>> I have a Writer document with about 50 rows in a table. At the bottom of the table, I want to sum the above column of cells in each row (a total). Currently, the only way to add a new row or rows to this sum is to add the cell reference(s) (i.e. |<J47>) to the long string of cell references to sum. Is this the only way, other than dragging from the first cell to the last cell?
>>
>> I tried =sum(J2:Jn) and even =sum(<J2>:<Jn>) but that just displays a formula error message in the sum cell.
>>
>> I would like to be able to add rows to this table as needed and have an easy way to add the new cells to the sum. A way of specifying a range of cells is perfect. As I now do it, the more rows I need to add, the more tedious and error-prone this process becomes.
>>
>> Thanks.
>>
>> Girvin Herr
>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Is there a way to simplify summing Writer table cells?

Brian Barker
In reply to this post by Girvin Herr-2
At 15:11 13/07/2017 -0700, Girvin Herr wrote:
>I have a Writer document with about 50 rows in a table. At the
>bottom of the table, I want to sum the above column of cells in each
>row (a total). Currently, the only way to add a new row or rows to
>this sum is to add the cell reference(s) (i.e. |<J47>) to the long
>string of cell references to sum. Is this the only way, other than
>dragging from the first cell to the last cell?

Dragging is surely not such a bind? If you have the formula =sum
<B1:B50> and you add a fifty-first row of data,
o Put the cursor into the cell containing the total.
o Press F2 to display the Input Line at the top (as you see in using
a spreadsheet). The Formula Text window still displays =sum <B1:B50> .
o Drag across the new range. The Formula Text (also shown in the
total cell) changes to =sum <B1:B51> .
o Either click the green "Apply" tick mark in the Input Line, or
simply press Enter.

>I would like to be able to add rows to this table as needed and have
>an easy way to add the new cells to the sum. A way of specifying a
>range of cells is perfect. As I now do it, the more rows I need to
>add, the more tedious and error-prone this process becomes.

This is problematic only if you choose or need to add rows *outside*
the existing range of rows to be summed - before the first row or
after the last row in the existing summed range, that is. This
suggests two further possibilities:

1. Insert your new row of data somewhere else - within the existing
range, that is. (You are given the option to insert new rows Before
instead of After, which may help.) If the order of your data in the
table rows is not significant, this may suffice.

2. Alternatively, if you want to maintain some order and insert your
new data, say, at the end of the existing material, here is a workaround:
o Insert your new row *before* the last row of the existing data - so
that it becomes the new fiftieth row of what are now fifty-one rows.
(The total cell's formula has been automatically modified to =sum <B1:B51> ).
o Select the entire fifty-first row (your original fiftieth row).
o Cut this data and paste it into the new fiftieth row.
o Add your new data to the now empty new fifty-first row.
No need to touch the formula.

I trust this helps.

Brian Barker


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Is there a way to simplify summing Writer table cells?

Steve Ahlers-2
In reply to this post by Girvin Herr-2
Girvin (sorry about the misspelling),

What formula do you get when you click and drag? That should give you the correct format that you can follow for other columns or in a spread sheet you can "copy" the result cell and "paste special" to other columns' result cells.

Sent from my iPhone

> On Jul 13, 2017, at 4:14 PM, Girvin Herr <[hidden email]> wrote:
>
> Steven,
>
> I just tried both, and with and without the <> and got the same results: **Expression is faulty **.
>
> Thanks for the help.
> Girvin
>
>
>
>> On 07/13/2017 03:47 PM, Steven Ahlers wrote:
>> Givin,
>>
>> Have you tried a "," or a ";" instead of a ":" ?
>>
>> Sent from my iPhone
>>
>>> On Jul 13, 2017, at 3:11 PM, Girvin Herr <[hidden email]> wrote:
>>>
>>> Greetings,
>>>
>>> I have a Writer document with about 50 rows in a table. At the bottom of the table, I want to sum the above column of cells in each row (a total). Currently, the only way to add a new row or rows to this sum is to add the cell reference(s) (i.e. |<J47>) to the long string of cell references to sum. Is this the only way, other than dragging from the first cell to the last cell?
>>>
>>> I tried =sum(J2:Jn) and even =sum(<J2>:<Jn>) but that just displays a formula error message in the sum cell.
>>>
>>> I would like to be able to add rows to this table as needed and have an easy way to add the new cells to the sum. A way of specifying a range of cells is perfect. As I now do it, the more rows I need to add, the more tedious and error-prone this process becomes.
>>>
>>> Thanks.
>>>
>>> Girvin Herr
>>>
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: [hidden email]
>>> For additional commands, e-mail: [hidden email]
>>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Is there a way to simplify summing Writer table cells?

Girvin Herr-3
In reply to this post by Brian Barker


On 07/13/2017 04:58 PM, Brian Barker wrote:

> At 15:11 13/07/2017 -0700, Girvin Herr wrote:
>> I have a Writer document with about 50 rows in a table. At the bottom
>> of the table, I want to sum the above column of cells in each row (a
>> total). Currently, the only way to add a new row or rows to this sum
>> is to add the cell reference(s) (i.e. |<J47>) to the long string of
>> cell references to sum. Is this the only way, other than dragging
>> from the first cell to the last cell?
>
> Dragging is surely not such a bind? If you have the formula =sum
> <B1:B50> and you add a fifty-first row of data,
> o Put the cursor into the cell containing the total.
> o Press F2 to display the Input Line at the top (as you see in using a
> spreadsheet). The Formula Text window still displays =sum <B1:B50> .
> o Drag across the new range. The Formula Text (also shown in the total
> cell) changes to =sum <B1:B51> .
Actually, no. What it changes to is =sum(<B1>|<B2>|...|<B51>). If I then
drag a new set of cells, the new range is appended to the long string of
cell references. That means that I must delete the original string
before dragging the new set. I just tried some experiments with dragging
and it is a mess - very error prone.
I did try you're example of =sum(<B1:B50>) and it produced the **
Expression is faulty ** message. However, when I selected the Sum icon
again and used the green check to okay it without changing anything, the
error went away and the correct sum was displayed again. I didn't really
do anything to the formula. Also, the <B1:B50> was changed to the
<B1>|<B2>|...|<B51> again. So it did not stick.

> o Either click the green "Apply" tick mark in the Input Line, or
> simply press Enter.
>
>> I would like to be able to add rows to this table as needed and have
>> an easy way to add the new cells to the sum. A way of specifying a
>> range of cells is perfect. As I now do it, the more rows I need to
>> add, the more tedious and error-prone this process becomes.
>
> This is problematic only if you choose or need to add rows *outside*
> the existing range of rows to be summed - before the first row or
> after the last row in the existing summed range, that is. This
> suggests two further possibilities:
>
> 1. Insert your new row of data somewhere else - within the existing
> range, that is. (You are given the option to insert new rows Before
> instead of After, which may help.) If the order of your data in the
> table rows is not significant, this may suffice.
I tried this and it worked. However, I got the error message again and
had to do the clicking on the sum icon and the green check icon again,
then it fixed itself. The good news is that this option is closest to
what I would like to see. I can insert as many rows as I need and still
get the sum to follow it without manually editing that long string of
cell references. It is a reasonable option to me.

>
> 2. Alternatively, if you want to maintain some order and insert your
> new data, say, at the end of the existing material, here is a workaround:
> o Insert your new row *before* the last row of the existing data - so
> that it becomes the new fiftieth row of what are now fifty-one rows.
> (The total cell's formula has been automatically modified to =sum
> <B1:B51> ).
> o Select the entire fifty-first row (your original fiftieth row).
> o Cut this data and paste it into the new fiftieth row.
> o Add your new data to the now empty new fifty-first row.
> No need to touch the formula.
>
Yes, I have done this in Calc. Until you mentioned it, I was not aware
it can also be done in Writer tables.
> I trust this helps.
>
> Brian Barker
>
Yes, Brian, it has helped.
Thanks.
Girvin

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Is there a way to simplify summing Writer table cells?

Brian Barker
At 17:38 13/07/2017 -0700, Girvin Herr wrote:

>On 07/13/2017 04:58 PM, Brian Barker wrote:
>>At 15:11 13/07/2017 -0700, Girvin Herr wrote:
>>>I have a Writer document with about 50 rows in a table. At the
>>>bottom of the table, I want to sum the above column of cells in
>>>each row (a total). Currently, the only way to add a new row or
>>>rows to this sum is to add the cell reference(s) (i.e. |<J47>) to
>>>the long string of cell references to sum. Is this the only way,
>>>other than dragging from the first cell to the last cell?
>>
>>Dragging is surely not such a bind? If you have the formula =sum
>><B1:B50> and you add a fifty-first row of data,
>>o Put the cursor into the cell containing the total.
>>o Press F2 to display the Input Line at the top (as you see in
>>using a spreadsheet). The Formula Text window still displays =sum <B1:B50> .
>>o Drag across the new range. The Formula Text (also shown in the
>>total cell) changes to =sum <B1:B51> .
>
>Actually, no. What it changes to is =sum(<B1>|<B2>|...|<B51>).

Er, not in my OpenOffice 4.1.3, it doesn't. I did test this
thoroughly before writing. I don't see how you could get anything
like this unless you had started with the cells referred to
separately and not as a range. And where did those parentheses come
from? There were none in my example. I strongly suspect that these
are what are causing you problems. (I've now seen how this has
happened: read on.)

>If I then drag a new set of cells, the new range is appended to the
>long string of cell references. That means that I must delete the
>original string before dragging the new set. I just tried some
>experiments with dragging and it is a mess - very error prone.

This is all down to your superfluous parentheses, I think.

>I did try your example of =sum(<B1:B50>) ...

No: that was not my example; you have added gratuitous parentheses again.

>... and it produced the ** Expression is faulty ** message.

It doesn't complain for me - despite those parentheses.

>However, when I selected the Sum icon again ...

Aha! I wasn't using the Sum icon, and it does indeed lead to a
formula listing the cells in the range individually - instead of as a
range. I don't see why this should be. This indeed makes subsequent
dragging problematic. And I now see where your parentheses come from.
Instead, try this:
o Put the cursor into the cell to contain the total.
o Press F2: you will see an equals sign in the Formula Text window.
o Add just the three letters "sum" - nothing more - to read "=sum".
(Er, certainly no parentheses!)
o Now just drag over the range of cells.

You should now see just =sum<B1:B50> , and the dragging I proposed
before should now work for modifications.

>>1. Insert your new row of data somewhere else - within the existing
>>range, that is. (You are given the option to insert new rows Before
>>instead of After, which may help.) If the order of your data in the
>>table rows is not significant, this may suffice.
>
>I tried this and it worked. However, I got the error message again
>and had to do the clicking on the sum icon and the green check icon
>again, then it fixed itself. The good news is that this option is
>closest to what I would like to see. I can insert as many rows as I
>need and still get the sum to follow it without manually editing
>that long string of cell references. It is a reasonable option to me.

I'd avoid the Sum icon.

>>2. Alternatively, if you want to maintain some order and insert
>>your new data, say, at the end of the existing material, here is a workaround:
>>o Insert your new row *before* the last row of the existing data -
>>so that it becomes the new fiftieth row of what are now fifty-one
>>rows. (The total cell's formula has been automatically modified to
>>=sum <B1:B51> ).
>>o Select the entire fifty-first row (your original fiftieth row).
>>o Cut this data and paste it into the new fiftieth row.
>>o Add your new data to the now empty new fifty-first row.
>>No need to touch the formula.
>
>Yes, I have done this in Calc. Until you mentioned it, I was not
>aware it can also be done in Writer tables.
>
>Yes, Brian, it has helped.

Good-oh!

Brian Barker  


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Is there a way to simplify summing Writer table cells?

Girvin Herr-2


On 07/14/2017 03:34 AM, Brian Barker wrote:

> <snip>
> Aha! I wasn't using the Sum icon, and it does indeed lead to a formula
> listing the cells in the range individually - instead of as a range. I
> don't see why this should be. This indeed makes subsequent dragging
> problematic. And I now see where your parentheses come from. Instead,
> try this:
> o Put the cursor into the cell to contain the total.
> o Press F2: you will see an equals sign in the Formula Text window.
> o Add just the three letters "sum" - nothing more - to read "=sum".
> (Er, certainly no parentheses!)
> o Now just drag over the range of cells.
>
> You should now see just =sum<B1:B50> , and the dragging I proposed
> before should now work for modifications.
> <snip>
> Good-oh!
>
> Brian Barker
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
Brian,
Yes! That worked. Now the =sum<B1:B50> formula sticks and is not
converted to the long form. I don't know where the long form started.
This is an old template that I have been using and I no longer remember
how I initially created the sum. However, now that I know how to use the
range form, it is sticky and consistent. I can simply edit the range
with F2 whenever I append new rows to the table. I also see that the
range changes with a new drag, rather than appending the new range to
the long form list. That is an option now too. So, the key is to stop
using the sum icon from the toolbar and use F2 instead.

Thanks much. Now I have to change my templates...
Girvin


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Is there a way to simplify summing Writer table cells?

Girvin Herr-2
In reply to this post by Steve Ahlers-2
Steve,

Thanks for your response. As you may have already seen from my response
to Brian's reply, the solution to my problem is to use F2 rather than
the sum icon from the toolbar. This produces and allows the formula
=sum<B1:B50> to be used and stick. The sum icon seems to convert such a
formula to the long form of =sum(<B1>|<B2>|...|<B50>), which is tedious
and error-prone to edit and change.

Thanks again.
Girvin


On 07/13/2017 05:02 PM, Steve Ahlers wrote:

> Girvin (sorry about the misspelling),
>
> What formula do you get when you click and drag? That should give you the correct format that you can follow for other columns or in a spread sheet you can "copy" the result cell and "paste special" to other columns' result cells.
>
> Sent from my iPhone
>
>> On Jul 13, 2017, at 4:14 PM, Girvin Herr <[hidden email]> wrote:
>>
>> Steven,
>>
>> I just tried both, and with and without the <> and got the same results: **Expression is faulty **.
>>
>> Thanks for the help.
>> Girvin
>>
>>
>>
>>> On 07/13/2017 03:47 PM, Steven Ahlers wrote:
>>> Givin,
>>>
>>> Have you tried a "," or a ";" instead of a ":" ?
>>>
>>> Sent from my iPhone
>>>
>>>> On Jul 13, 2017, at 3:11 PM, Girvin Herr <[hidden email]> wrote:
>>>>
>>>> Greetings,
>>>>
>>>> I have a Writer document with about 50 rows in a table. At the bottom of the table, I want to sum the above column of cells in each row (a total). Currently, the only way to add a new row or rows to this sum is to add the cell reference(s) (i.e. |<J47>) to the long string of cell references to sum. Is this the only way, other than dragging from the first cell to the last cell?
>>>>
>>>> I tried =sum(J2:Jn) and even =sum(<J2>:<Jn>) but that just displays a formula error message in the sum cell.
>>>>
>>>> I would like to be able to add rows to this table as needed and have an easy way to add the new cells to the sum. A way of specifying a range of cells is perfect. As I now do it, the more rows I need to add, the more tedious and error-prone this process becomes.
>>>>
>>>> Thanks.
>>>>
>>>> Girvin Herr
>>>>
>>>>
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: [hidden email]
>>>> For additional commands, e-mail: [hidden email]
>>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: [hidden email]
>>> For additional commands, e-mail: [hidden email]
>>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
>>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]