Re: [libreoffice-users] 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: users+unsubscribe@global.libreoffice.org
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