Date: prev next · Thread: first prev next last
2015 Archives by date, by thread · List index


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

Context


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.