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

On Feb 13, 2015, at 9:41 AM, Tom Davies <> 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.  


Regards from
Tom :)

On 13 February 2015 at 11:52, Kaj <> 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:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


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.