Calc fails to warn when insert cell breaks sum(A1:A2)

classic Classic list List threaded Threaded
9 messages Options
Spencer Graves-2 Spencer Graves-2
Reply | Threaded
Open this post in threaded view
|

Calc fails to warn when insert cell breaks sum(A1:A2)

I recently noticed that a complicated spreadsheet that had previously functioned correctly was giving wrong answers without warning.  After the usual wailing and gnashing of teeth, I traced the problem to a cell containing "=C4-SUM(G11:G1016)”.  Further experimentation produced the following simple version of the problem:  


(1) Let A1=1, A2=2, and A3=sum(A1:A2);  A3 computes here as 3.
(2) Insert cell A1 shift right.
(3) Observe: A3 now computes as 2. This is obvious in this case but far from obvious in a complicated spreadsheet, where the connection between A1 and A3 is obscure. In such cases, For an insert that would cause an error in a reference like A1:A2, I believe that Calc should issue a warning something like, “WARNING: Insert may change the answer computed in A3. Do you want to proceed?” I further think there should be no default and the user should be forced to select either “Yes” or “No”.  


This was observed in LO 4.3.5.2, LO 4..5.0.0.alpha0 2015-02-05 00:36:56, and MS Excell 2003 sp3.


Should this be filed as a bug report or a feature request?  If yes, which, and what message should display?  


Wikipedia says, "A software bug is an error, flaw, failure, or fault in a computer program or system that causes it to produce an incorrect or unexpected result, or to behave in unintended ways.”  I think this fits that definition.  However, it may qualify as a feature request, because the fix is less than obvious (and it has been around for so long).  


Enjoy, Spencer


--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

Re: Calc fails to warn when insert cell breaks sum(A1:A2)

At 12:14 12/02/2015 -0800, Spencer Graves wrote:

>I recently noticed that a complicated spreadsheet that had
>previously functioned correctly was giving wrong answers without
>warning. After the usual wailing and gnashing of teeth, I traced the
>problem to a cell containing "=C4-SUM(G11:G1016)". Further
>experimentation produced the following simple version of the problem:
>
>(1) Let A1=1, A2=2, and A3=sum(A1:A2); A3 computes here as 3.
>(2) Insert cell A1 shift right.
>(3) Observe: A3 now computes as 2. This is obvious in this case but
>far from obvious in a complicated spreadsheet, where the connection
>between A1 and A3 is obscure. In such cases, For an insert that
>would cause an error in a reference like A1:A2, I believe that Calc
>should issue a warning something like, "WARNING: Insert may change
>the answer computed in A3. Do you want to proceed?" I further think
>there should be no default and the user should be forced to select
>either "Yes" or "No".

Sorry, but I do not see how you can claim that the formula in A3 is
"broken": it remains as =SUM(A1:A2) exactly as you entered it. What
has changed is that you have displaced your data and made the result
of the formula correctly different. I'm glad that Calc has allowed
you to do this. If a spreadsheet program warned you when any
calculated results might change, you would have to confirm just about
every entry or change.

>This was observed in LO 4.3.5.2, LO 4..5.0.0.alpha0 2015-02-05
>00:36:56, and MS Excel 2003 sp3.

And perhaps in every other spreadsheet ever created?

>Should this be filed as a bug report or a feature request?

Neither, I hope.

>Wikipedia says, "A software bug is an error, flaw, failure, or fault
>in a computer program or system that causes it to produce an
>incorrect or unexpected result, or to behave in unintended ways." I
>think this fits that definition.

I don't see that the result is incorrect and I'm sure the behaviour
is not unintended. I accept that you see this as an unexpected
result, but then expectation is in the eye of the beholder.

A couple of points:

o Spreadsheets are useful only when the (usually hidden) formulae are
appropriate and there is generally no way to ensure that this is so.
Consequently spreadsheets are a fragile way to construct a means of
computation. This is perhaps unfortunate but nevertheless true.

o One helpful technique might be, after selecting A1 and before
creating the problem by displacing its value, to use Tools |
Detective > | Trace Dependents to show where any dependent formulae
are. This might help you to rethink your change.

I trust this helps.

Brian Barker


--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

TomD TomD
Reply | Threaded
Open this post in threaded view
|

Re: Calc fails to warn when insert cell breaks sum(A1:A2)

In reply to this post by Spencer Graves-2
Hi :)
I think post as a "Feature Request".  If at all possible i think it's
best to post "Feature Requests" instead of bug-reports.  They have a
much bigger feel-good factor and i suspect they are more likely to
attract new devs.

I think there has got to be a default and i tend to prefer it if there
is one.  It can be a pain when you know the pop-up and have to do more
than just hit enter all the time.
Regards form
Tom :)




On 12 February 2015 at 20:14, Spencer Graves
<[hidden email]> wrote:

> I recently noticed that a complicated spreadsheet that had previously functioned correctly was giving wrong answers without warning.  After the usual wailing and gnashing of teeth, I traced the problem to a cell containing "=C4-SUM(G11:G1016)”.  Further experimentation produced the following simple version of the problem:
>
>
> (1) Let A1=1, A2=2, and A3=sum(A1:A2);  A3 computes here as 3.
> (2) Insert cell A1 shift right.
> (3) Observe: A3 now computes as 2. This is obvious in this case but far from obvious in a complicated spreadsheet, where the connection between A1 and A3 is obscure. In such cases, For an insert that would cause an error in a reference like A1:A2, I believe that Calc should issue a warning something like, “WARNING: Insert may change the answer computed in A3. Do you want to proceed?” I further think there should be no default and the user should be forced to select either “Yes” or “No”.
>
>
> This was observed in LO 4.3.5.2, LO 4..5.0.0.alpha0 2015-02-05 00:36:56, and MS Excell 2003 sp3.
>
>
> Should this be filed as a bug report or a feature request?  If yes, which, and what message should display?
>
>
> Wikipedia says, "A software bug is an error, flaw, failure, or fault in a computer program or system that causes it to produce an incorrect or unexpected result, or to behave in unintended ways.”  I think this fits that definition.  However, it may qualify as a feature request, because the fix is less than obvious (and it has been around for so long).
>
>
> Enjoy, Spencer
>
>
> --
> To unsubscribe e-mail to: [hidden email]
> Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
> List archive: http://listarchives.libreoffice.org/global/users/
> All messages sent to this list will be publicly archived and cannot be deleted

--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
libreoffice-ml.mbourne libreoffice-ml.mbourne
Reply | Threaded
Open this post in threaded view
|

Re: Calc fails to warn when insert cell breaks sum(A1:A2)

In reply to this post by Brian Barker
Brian Barker wrote:

> At 12:14 12/02/2015 -0800, Spencer Graves wrote:
>> I recently noticed that a complicated spreadsheet that had previously
>> functioned correctly was giving wrong answers without warning. After
>> the usual wailing and gnashing of teeth, I traced the problem to a
>> cell containing "=C4-SUM(G11:G1016)". Further experimentation produced
>> the following simple version of the problem:
>>
>> (1) Let A1=1, A2=2, and A3=sum(A1:A2); A3 computes here as 3.
>> (2) Insert cell A1 shift right.
>> (3) Observe: A3 now computes as 2. This is obvious in this case but
>> far from obvious in a complicated spreadsheet, where the connection
>> between A1 and A3 is obscure. In such cases, For an insert that would
>> cause an error in a reference like A1:A2, I believe that Calc should
>> issue a warning something like, "WARNING: Insert may change the answer
>> computed in A3. Do you want to proceed?" I further think there should
>> be no default and the user should be forced to select either "Yes" or
>> "No".
>
> Sorry, but I do not see how you can claim that the formula in A3 is
> "broken": it remains as =SUM(A1:A2) exactly as you entered it. What has
> changed is that you have displaced your data and made the result of the
> formula correctly different. I'm glad that Calc has allowed you to do
> this. If a spreadsheet program warned you when any calculated results
> might change, you would have to confirm just about every entry or change.
>
>> This was observed in LO 4.3.5.2, LO 4..5.0.0.alpha0 2015-02-05
>> 00:36:56, and MS Excel 2003 sp3.
>
> And perhaps in every other spreadsheet ever created?
>
>> Should this be filed as a bug report or a feature request?
>
> Neither, I hope.
>
>> Wikipedia says, "A software bug is an error, flaw, failure, or fault
>> in a computer program or system that causes it to produce an incorrect
>> or unexpected result, or to behave in unintended ways." I think this
>> fits that definition.
>
> I don't see that the result is incorrect and I'm sure the behaviour is
> not unintended. I accept that you see this as an unexpected result, but
> then expectation is in the eye of the beholder.
>
> A couple of points:
>
> o Spreadsheets are useful only when the (usually hidden) formulae are
> appropriate and there is generally no way to ensure that this is so.
> Consequently spreadsheets are a fragile way to construct a means of
> computation. This is perhaps unfortunate but nevertheless true.
>
> o One helpful technique might be, after selecting A1 and before creating
> the problem by displacing its value, to use Tools | Detective > | Trace
> Dependents to show where any dependent formulae are. This might help you
> to rethink your change.
>
> I trust this helps.
>
> Brian Barker

Another point to add... If the problem is that spreadsheet has been
carefully created, but used by less skilled users (who change values
they shouldn't, overwrite formulas, insert extra cells where they break
formulas etc.), you can guard against that with cell protection. By
default, all cells are marked to be protected if the sheet is protected,
although the sheet is not protected by default. Steps to make use of
this are:
- Create the spreadsheet as normal
- For each cell or range of cells which the user needs to modify:
   - Select the cell (or range of cells)
   - Format > Cells > Cell Protection > Untick "Protected" > OK
- For each sheet containing cells to be protected:
   - Tools > Protect Document > Sheet, optionally enter a password > OK

If you later need to make changes to the protected cells, go to Tools >
Protect Document > Sheet. You'll have to enter the password if you set
one (so don't forget it!) Once you're done, enable the protection again
(entering the password again if you want to use one).

If you don't set a password, the sheet will still be protected against
accidental changes, but can be unprotected without entering a password
so doesn't stop people who are determined to break it. That's probably
good enough for most purposes. Then again, I doubt the protection is
very secure anyway (unless the document is somehow encrypted to allow
reading but not modification of the protected cells), so while it would
take a bit more skill and determination to get around a password it may
still be possible.

I'd suggest not using the same password as you do for anything else that
needs to be secure (not that you'd use the same password for more than
one thing anyway, of course...). Older versions of MS Office stored its
equivalent of that password in the file with a relatively simple
obfuscation, so it was possible to extract the password from the file.
LibreOffice and newer versions of MS Office are probably better, but I
still wouldn't like to bet my bank account on it ;o)

Mark.


--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Kaj Persson-2 Kaj Persson-2
Reply | Threaded
Open this post in threaded view
|

Re: Calc fails to warn when insert cell breaks sum(A1:A2)

In reply to this post by Spencer Graves-2
I think have a wee difficult to understand what you are doing, as I do
not see any error. You put constants 1 and 2 in the cells A1 and A2 and
a sum formula in A3. Then you insert an empty cell in A1 while moving
the existing content in the cells one step to the right. Hence after the
insertion A2 contains the constant 1, A3 contains the constant 2 and A4
contains the formula. All references are relative, so cell A4 now is =
sum(A2:A3) giving the result 3, just as before. That the cell A3
computes 2 is evident as it contains the constant you put in cell A2
before the move.

So sorry, I am not clever enough to realize your problem.


Den 2015-02-12 21:14, skrev Spencer Graves:

> I recently noticed that a complicated spreadsheet that had previously functioned correctly was giving wrong answers without warning.  After the usual wailing and gnashing of teeth, I traced the problem to a cell containing "=C4-SUM(G11:G1016)”.  Further experimentation produced the following simple version of the problem:
>
>
> (1) Let A1=1, A2=2, and A3=sum(A1:A2);  A3 computes here as 3.
> (2) Insert cell A1 shift right.
> (3) Observe: A3 now computes as 2. This is obvious in this case but far from obvious in a complicated spreadsheet, where the connection between A1 and A3 is obscure. In such cases, For an insert that would cause an error in a reference like A1:A2, I believe that Calc should issue a warning something like, “WARNING: Insert may change the answer computed in A3. Do you want to proceed?” I further think there should be no default and the user should be forced to select either “Yes” or “No”.
>
>
> This was observed in LO 4.3.5.2, LO 4..5.0.0.alpha0 2015-02-05 00:36:56, and MS Excell 2003 sp3.
>
>
> Should this be filed as a bug report or a feature request?  If yes, which, and what message should display?
>
>
> Wikipedia says, "A software bug is an error, flaw, failure, or fault in a computer program or system that causes it to produce an incorrect or unexpected result, or to behave in unintended ways.”  I think this fits that definition.  However, it may qualify as a feature request, because the fix is less than obvious (and it has been around for so long).
>
>
> Enjoy, Spencer
>
>


--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
TomD TomD
Reply | Threaded
Open this post in threaded view
|

Re: Calc fails to warn when insert cell breaks sum(A1:A2)

Hi :)
I'm guessing you are a fairly logical and astute thinker rather than a
typical office drone or a typically tech-averse accountancy person.

Yes there is no problem unless the user is a moron.  Sadly many of us
often are a bit moronic from time to time.

The suggestion was to maybe write a feature request to guard against
user-error or pebkac problems.
Regards from
Tom :)


On 13 February 2015 at 00:14, Kaj <[hidden email]> wrote:

> I think have a wee difficult to understand what you are doing, as I do not
> see any error. You put constants 1 and 2 in the cells A1 and A2 and a sum
> formula in A3. Then you insert an empty cell in A1 while moving the existing
> content in the cells one step to the right. Hence after the insertion A2
> contains the constant 1, A3 contains the constant 2 and A4 contains the
> formula. All references are relative, so cell A4 now is = sum(A2:A3) giving
> the result 3, just as before. That the cell A3 computes 2 is evident as it
> contains the constant you put in cell A2 before the move.
>
> So sorry, I am not clever enough to realize your problem.
>
>
> Den 2015-02-12 21:14, skrev Spencer Graves:
>>
>> I recently noticed that a complicated spreadsheet that had previously
>> functioned correctly was giving wrong answers without warning.  After the
>> usual wailing and gnashing of teeth, I traced the problem to a cell
>> containing "=C4-SUM(G11:G1016)”.  Further experimentation produced the
>> following simple version of the problem:
>>
>>
>> (1) Let A1=1, A2=2, and A3=sum(A1:A2);  A3 computes here as 3.
>> (2) Insert cell A1 shift right.
>> (3) Observe: A3 now computes as 2. This is obvious in this case but far
>> from obvious in a complicated spreadsheet, where the connection between A1
>> and A3 is obscure. In such cases, For an insert that would cause an error in
>> a reference like A1:A2, I believe that Calc should issue a warning something
>> like, “WARNING: Insert may change the answer computed in A3. Do you want to
>> proceed?” I further think there should be no default and the user should be
>> forced to select either “Yes” or “No”.
>>
>>
>> This was observed in LO 4.3.5.2, LO 4..5.0.0.alpha0 2015-02-05 00:36:56,
>> and MS Excell 2003 sp3.
>>
>>
>> Should this be filed as a bug report or a feature request?  If yes, which,
>> and what message should display?
>>
>>
>> Wikipedia says, "A software bug is an error, flaw, failure, or fault in a
>> computer program or system that causes it to produce an incorrect or
>> unexpected result, or to behave in unintended ways.”  I think this fits that
>> definition.  However, it may qualify as a feature request, because the fix
>> is less than obvious (and it has been around for so long).
>>
>>
>> Enjoy, Spencer
>>
>>
>
>
> --
> To unsubscribe e-mail to: [hidden email]
> Problems?
> http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
> List archive: http://listarchives.libreoffice.org/global/users/
> All messages sent to this list will be publicly archived and cannot be
> deleted

--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Kaj Persson-2 Kaj Persson-2
Reply | Threaded
Open this post in threaded view
|

Re: Calc fails to warn when insert cell breaks sum(A1:A2)

In reply to this post by Kaj Persson-2
First: I missed sending to the list, so here again is my posting.

Ok, I admit. I am the moron. Still I do not see the problem. Calc does
not behave the way describe. If you insert a new cell, all its
neighbours are influenced, and you yourself chose how, via the dialogue:
"Move down ", "Move right", "New line" or "New column" (ok I did not
quote the headers correctly, but I am convinced you understand). No
other option is given. So after a cell insertion with option "Move
right" the neighbours really have new positions one step ahead of the
original one. To me, what you describe, Brian, the situation is not
inserting a new cell, but a new value, possibly clearing the old one,
into cell A1, without changing the structure. Am I correct? If so, the
solution is already given by Mark in this thread, namely cell
protection. If this is done in an appropriate way, and the user changes
the value an allowed cell, no spreadsheet program in this world can
hinder that (or warn for it).

I honestly try to understand the core of the original question, but I
cannot, sorry.



At 2015-02-13 04:27, Brian Barker wrote:

> At 01:14 13/02/2015 +0100, you wrote:
>> I think have a wee difficult to understand what you are doing, as I
>> do not see any error. You put constants 1 and 2 in the cells A1 and
>> A2 and a sum formula in A3. Then you insert an empty cell in A1 while
>> moving the existing content in the cells one step to the right. Hence
>> after the insertion A2 contains the constant 1, A3 contains the
>> constant 2 and A4 contains the formula. All references are relative,
>> so cell A4 now is = sum(A2:A3) giving the result 3, just as before.
>> That the cell A3 computes 2 is evident as it contains the constant
>> you put in cell A2 before the move.
>
> You are right that there is no problem to be solved here, but you've
> misunderstood the detail of the problem. Although you talk of moving
> content "one step to the right", your subsequent description is of the
> situation if the insertion into A1 moves the rest of column A *down*.
>
> Instead, the questioner means what he says: he inserts a new, empty
> cell into A1, moving the whole of row 1 one place to the right. A2
> still has 2 and A3 still has =SUM(A1:A2), so the formula now adds the
> 2 in A2 to the value of the new empty cell A1 - interpreted as zero,
> of course.
>
> Brian Barker - privately
>


--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

TomD TomD
Reply | Threaded
Open this post in threaded view
|

Re: Calc fails to warn when insert cell breaks sum(A1:A2)

Hi :)
Ok, you are not being a moron.  I wasn't trying to be funny of anything.
The software is doing exactly the right thing and even has a little pop-up
that people have to choose which way the existing cells have to go.  So it
really shouldn't be a problem at all.

However people often create spreadsheets to do things that would probably
be best done in some other program.

A classic is to have a long list of numbers, and then those numbers
repeated but split out across a table into different columns.  Sometimes
this is done using equations and codes but usually that just confuses the
sorts of people who end-up typing the numbers in.  Then each column is
totalled-up and the total of the columns 'should' match the total of the
long column, of course.

This type of Cash-book approach shows the total spent on each budget
heading and then also the total amount for the period.  Ideally it would be
done in GnuCash, Sage Instant/Line50, Quickbooks or some-such.  However
those are quite expensive (except GnuCash which is free and therefore
usually assumed to be not-as-good) and then there are expensive training
programs on how to use the expensive ones.  So it's a lot cheaper for
people (and easier for them to understand)  to just start typing numbers
into a spreadsheet and then kinda muddle along from there.

This only sometimes leads to problems, such as the sum of the whole list
not being the same as the total of the budget headings or not matching cash
in the bank (after eliminating expected payments and expected income that
either isn't quite on the bank statement or hasn't been typed into the
cash-book yet).  However when a problem DOES happen it's usually quite
tricky to find out where things went wrong because everything still looks
quite neat in a print-out even if the figures typed in were horribly wrong
(we all make tpyos right?).


So the problem is that when people insert a few cells they might well not
realise that they are also moving cells that are part of some calculation
either at the bottom of the sheet or elsewhere - and they might not be
thinking about that because they are trying to juggle too many other points
at the same time (such as is this zero-rated for tax or exempt or at a
different level and does some of the payment have to go in one cell but a
bit more of it appear in other columns and does this fit this column/budget
heading or that one?)

So, yes it's a user-problem, a classic pebcak but it can often be a
reasonable error.
Regards from
Tom :)



On 13 February 2015 at 11:52, Kaj <[hidden email]> wrote:

> First: I missed sending to the list, so here again is my posting.
>
> Ok, I admit. I am the moron. Still I do not see the problem. Calc does not
> behave the way describe. If you insert a new cell, all its neighbours are
> influenced, and you yourself chose how, via the dialogue: "Move down ",
> "Move right", "New line" or "New column" (ok I did not quote the headers
> correctly, but I am convinced you understand). No other option is given. So
> after a cell insertion with option "Move right" the neighbours really have
> new positions one step ahead of the original one. To me, what you describe,
> Brian, the situation is not inserting a new cell, but a new value, possibly
> clearing the old one, into cell A1, without changing the structure. Am I
> correct? If so, the solution is already given by Mark in this thread,
> namely cell protection. If this is done in an appropriate way, and the user
> changes the value an allowed cell, no spreadsheet program in this world can
> hinder that (or warn for it).
>
> I honestly try to understand the core of the original question, but I
> cannot, sorry.
>
>
>
> At 2015-02-13 04:27, Brian Barker wrote:
>
>> At 01:14 13/02/2015 +0100, you wrote:
>>
>>> I think have a wee difficult to understand what you are doing, as I do
>>> not see any error. You put constants 1 and 2 in the cells A1 and A2 and a
>>> sum formula in A3. Then you insert an empty cell in A1 while moving the
>>> existing content in the cells one step to the right. Hence after the
>>> insertion A2 contains the constant 1, A3 contains the constant 2 and A4
>>> contains the formula. All references are relative, so cell A4 now is =
>>> sum(A2:A3) giving the result 3, just as before. That the cell A3 computes 2
>>> is evident as it contains the constant you put in cell A2 before the move.
>>>
>>
>> You are right that there is no problem to be solved here, but you've
>> misunderstood the detail of the problem. Although you talk of moving
>> content "one step to the right", your subsequent description is of the
>> situation if the insertion into A1 moves the rest of column A *down*.
>>
>> Instead, the questioner means what he says: he inserts a new, empty cell
>> into A1, moving the whole of row 1 one place to the right. A2 still has 2
>> and A3 still has =SUM(A1:A2), so the formula now adds the 2 in A2 to the
>> value of the new empty cell A1 - interpreted as zero, of course.
>>
>> Brian Barker - privately
>>
>>
>
> --
> To unsubscribe e-mail to: [hidden email]
> Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-
> unsubscribe/
> Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
> List archive: http://listarchives.libreoffice.org/global/users/
> All messages sent to this list will be publicly archived and cannot be
> deleted
>
>

--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Spencer Graves-2 Spencer Graves-2
Reply | Threaded
Open this post in threaded view
|

Re: Calc fails to warn when insert cell breaks sum(A1:A2)


> On Feb 13, 2015, at 9:41 AM, Tom Davies <[hidden email]> wrote:
>
> Hi :)
> Ok, you are not being a moron.  I wasn't trying to be funny of anything.
> The software is doing exactly the right thing and even has a little pop-up
> that people have to choose which way the existing cells have to go.  So it
> really shouldn't be a problem at all.
>
> However people often create spreadsheets to do things that would probably
> be best done in some other program.
>
> A classic is to have a long list of numbers, and then those numbers
> repeated but split out across a table into different columns.  Sometimes
> this is done using equations and codes but usually that just confuses the
> sorts of people who end-up typing the numbers in.  Then each column is
> totalled-up and the total of the columns 'should' match the total of the
> long column, of course.
>
> This type of Cash-book approach shows the total spent on each budget
> heading and then also the total amount for the period.  Ideally it would be
> done in GnuCash, Sage Instant/Line50, Quickbooks or some-such.  However
> those are quite expensive (except GnuCash which is free and therefore
> usually assumed to be not-as-good) and then there are expensive training
> programs on how to use the expensive ones.  So it's a lot cheaper for
> people (and easier for them to understand)  to just start typing numbers
> into a spreadsheet and then kinda muddle along from there.
>
> This only sometimes leads to problems, such as the sum of the whole list
> not being the same as the total of the budget headings or not matching cash
> in the bank (after eliminating expected payments and expected income that
> either isn't quite on the bank statement or hasn't been typed into the
> cash-book yet).  However when a problem DOES happen it's usually quite
> tricky to find out where things went wrong because everything still looks
> quite neat in a print-out even if the figures typed in were horribly wrong
> (we all make tpyos right?).
>
>
> So the problem is that when people insert a few cells they might well not
> realise that they are also moving cells that are part of some calculation
> either at the bottom of the sheet or elsewhere - and they might not be
> thinking about that because they are trying to juggle too many other points
> at the same time (such as is this zero-rated for tax or exempt or at a
> different level and does some of the payment have to go in one cell but a
> bit more of it appear in other columns and does this fit this column/budget
> heading or that one?)
>
> So, yes it's a user-problem, a classic pebcak but it can often be a
> reasonable error.


          Yes, but we can modify software to make it easier to use, like issuing a warning when a change that would break a multi-cell reference like this would produce "an incorrect or unexpected result" (usually called a bug).  If so, the default behavior should be to issue a warning before producing the said "incorrect or unexpected result", with the current behavior as the default option.  [Thanks to Tom(?) for arguing effectively for this default.]  


          Such a feature request would be appropriate if the vast majority of changes that would break a multi-cell reference like this are unintended.  Does anyone seriously suggest that's not the case?  


          Thanks to all who commented.  


          Spencer


> Regards from
> Tom :)
>
>
>
> On 13 February 2015 at 11:52, Kaj <[hidden email]> wrote:
>
>> First: I missed sending to the list, so here again is my posting.
>>
>> Ok, I admit. I am the moron. Still I do not see the problem. Calc does not
>> behave the way describe. If you insert a new cell, all its neighbours are
>> influenced, and you yourself chose how, via the dialogue: "Move down ",
>> "Move right", "New line" or "New column" (ok I did not quote the headers
>> correctly, but I am convinced you understand). No other option is given. So
>> after a cell insertion with option "Move right" the neighbours really have
>> new positions one step ahead of the original one. To me, what you describe,
>> Brian, the situation is not inserting a new cell, but a new value, possibly
>> clearing the old one, into cell A1, without changing the structure. Am I
>> correct? If so, the solution is already given by Mark in this thread,
>> namely cell protection. If this is done in an appropriate way, and the user
>> changes the value an allowed cell, no spreadsheet program in this world can
>> hinder that (or warn for it).
>>
>> I honestly try to understand the core of the original question, but I
>> cannot, sorry.
>>
>>
>>
>> At 2015-02-13 04:27, Brian Barker wrote:
>>
>>> At 01:14 13/02/2015 +0100, you wrote:
>>>
>>>> I think have a wee difficult to understand what you are doing, as I do
>>>> not see any error. You put constants 1 and 2 in the cells A1 and A2 and a
>>>> sum formula in A3. Then you insert an empty cell in A1 while moving the
>>>> existing content in the cells one step to the right. Hence after the
>>>> insertion A2 contains the constant 1, A3 contains the constant 2 and A4
>>>> contains the formula. All references are relative, so cell A4 now is =
>>>> sum(A2:A3) giving the result 3, just as before. That the cell A3 computes 2
>>>> is evident as it contains the constant you put in cell A2 before the move.
>>>>
>>>
>>> You are right that there is no problem to be solved here, but you've
>>> misunderstood the detail of the problem. Although you talk of moving
>>> content "one step to the right", your subsequent description is of the
>>> situation if the insertion into A1 moves the rest of column A *down*.
>>>
>>> Instead, the questioner means what he says: he inserts a new, empty cell
>>> into A1, moving the whole of row 1 one place to the right. A2 still has 2
>>> and A3 still has =SUM(A1:A2), so the formula now adds the 2 in A2 to the
>>> value of the new empty cell A1 - interpreted as zero, of course.
>>>
>>> Brian Barker - privately
>>>
>>>
>>
>> --
>> To unsubscribe e-mail to: [hidden email]
>> Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-
>> unsubscribe/
>> Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
>> List archive: http://listarchives.libreoffice.org/global/users/
>> All messages sent to this list will be publicly archived and cannot be
>> deleted
>>
>>
>
> --
> To unsubscribe e-mail to: [hidden email]
> Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
> List archive: http://listarchives.libreoffice.org/global/users/
> All messages sent to this list will be publicly archived and cannot be deleted


--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted