Why do references expand when new rows are inserted above the summed range?

[ArvindKainth]

I have not selected Expand reference when new rows are inserted still Calc expand cell references in sum formula here is an example:

I have a table as below:

|      |         A       |
--------------------------
|  4   |  Particulars    |
|  5   |         2       |
|  6   |         3       |
|  7   |         1       |
|  8   | =sum(A5:A7)     |

I am inserting 5 new rows with following steps:

1. Selecting cell area A4 to A8
2. Right click on selected cell area and press insert - entire row
3. Result is 5 new rows inserted and exiting table shifted below normally
4. But in sum formula at A13 =sum(A5:A12) auto cell reference expend happen and i have not enable this feature

It should not consider Expand reference in this case, normally 5 rows must be inserted with no formula change. This is general practice users copy the tables by inserting rows above or under the existing tables.

[ROSt53]

When you use the function sum(a5:a7) and add rows into the range between a5 and a7 you always have the new rows included. The function is design for automatically including the new rows.

Depending on where you want to insert your new rows you could add each single cell or one or more ranges to create the sum you finally want to have. This means you can exclude the cells you do not have included in the sum.

Please not that it is not clear why you select the range a4 to a8 and how many rows did you add. Looking at your formulas sum(a5:a8) and sum(a5:a13) you must have added rows after row 5 or before row 7 to get the formula sum(a5:a13)

[ArvindKainth]

Hello ROS,

Thanks for your support.

I have gone through your reply and I agree if new row inserted between the defined range of sum formula (A5:A7) cell reference must be expanded but if I’m inserting rows before the range it must not expand.

Here is reply why I am selecting A4:A8
I want to insert 5 new rows above my table “A4:A8” and by selecting and inserting I can add 5 rows like just above my table at a time with same quantity of rows. Rows also can be added by going to above my table inserting one by one but this is lengthy process.

Take an example there is a form having 25 rows and user need to make same form above that then he has to add first 25 rows one by one and make copy above the existing table this would be lengthy job and also every time he has to count how many rows need to insert above as forms may have different quantity rows every time.

[Lupp]

Good reason to have a spare row in such cases. Solution if not: Insert a row above your “row of result”, Drag the wanted content one row upwards, Insert additional rows above the now empty one. Never indicate the number of rows choosing some already included in a range refered to by the result formula.
Please don’t think of a ‘feature request’ “Smart software” (let aside telepathy) will never be smart enough to know for any specific user what he wants. We’re better off telling ourselves.

[oweng]

Given the example, selecting A4:A8 and inserting rows, results in this value in A13:

=SUM(A10:A12) (Expand reference when new rows are inserted option off)

=SUM(A5:A12) (Expand reference when new rows are inserted option on)

This behaviour is consistent across v3.5.7.2, v4.1.6.2, v4.2.5.2, and v4.3.0.2 under GNU/Linux so would appear to be by design.