Re: [libreoffice-users] Calc fails to warn when insert cell breaks sum(A1:A2)

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: 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