Inserting a row does not change formula

users
#1

Running LO 3.5.7.2 on Ubuntu 12.04

If I have cells:

A1 = 2
A2 = 2
A3 = 2
A4 = =SUM(A1:A3) which equals 6

If I right click on the number 2 to the left of cell A2 to select that row and then insert a row from the popup menu and enter the number 2 in that blank cell, I get:

A1 = 2
A2 = 2
A3 = 2
A4 = 2
A5 = =SUM(A1:A4) which equals 8

If instead I right click on the number 4 to the left of cell A4 to select that row and then insert a row from the popup menu and enter the number 2 in that blank cell, I get:

A5 = =SUM(A1:A3) which equals 6.

If you need to insert a new row in a column of figures, right above the cell where you calculate a total seems to be the natural place to do it. So why doesn't the formula update when you do so?

Thanks, Jim

#2

Maybe this option can help:

Menu/Tools/Options/LibreOffic calc/General - Expand references when new
columns/rows are inserted.

Miguel Ángel.

#3

Thank you, that worked.

I'm curious why without that setting I can insert a row in the middle of a column of numbers and it expands correctly, but if I insert at the very top or very bottom row it does not?

Regards, Jim

#4

Hi :slight_smile:
Excel does exactly the same thing. When i show/train people to use
spreadsheets i try to get them to make the row above the totals row
much smaller so that people leave that row blank. Generally people
don't have time for that sort of thing and then have to spend hours
trying to work out why their formulas don't work. I've never
understood why spreadsheet programs are designed that way.
Regards from
tom :slight_smile:

#5

I realize this thread is a little long in the tooth by now but one aspect (why?) of Mr Byrnes query is still dangling.

Running LO 3.5.7.2 on Ubuntu 12.04

If I have cells:

A1 = 2
A2 = 2
A3 = 2
A4 = =SUM(A1:A3) which equals 6

If I right click on the number 2 to the left of cell A2 to select that
row and then insert a row from the popup menu and enter the number 2 in

that blank cell, I get:

A1 = 2
A2 = 2
A3 = 2
A4 = 2
A5 = =SUM(A1:A4) which equals 8

If instead I right click on the number 4 to the left of cell A4 to
select that row and then insert a row from the popup menu and enter the

number 2 in that blank cell, I get:

A5 = =SUM(A1:A3) which equals 6.

If you need to insert a new row in a column of figures, right above the

cell where you calculate a total seems to be the natural place to do
it.
So why doesn't the formula update when you do so?

In the first example cell A3 became cell A4 so the function call became SUM(A1:A4) but in the second example cell A3 remained where it started so there was no reason to alter the function call. Let's suppose that that function call was elsewhere on the sheet (e.g. G1) instead of directly below the numbers being summed. There would be no expectation for the function call to be adjusted. Right? I found the answer that pointed to an option that handles this issue when adding a row to be educational.