Ask Your Question
0

Inserting a row changes formulas

asked 2015-07-16 23:17:49 +0200

supradave gravatar image

I have a formula in, let's say A1, =sum(a5:a500). I have a range of data starting at A5, e.g. 1, 1, 1, 1, 1. So =sum(a5:a500) = 5. I want to insert a row at row 5 and add a new number but when I do, that changes the A1 formula to =sum(a6:a501). I've tried $a$5:$a$500, but that doesn't seem to solve the problem.

Probably just missing something.

edit retag flag offensive close merge delete

5 Answers

Sort by » oldest newest most voted
1

answered 2015-07-17 00:08:06 +0200

Alex Kemp gravatar image

updated 2015-07-17 01:22:22 +0200

The '$' (to declare an "absolute reference") becomes effective when you copy a formula, not when the formula is moved due to adding (or deleting) a row and/or column.

Let's take your example with 450 formulae / column pointed at a single cell. You now need to add another row, and all 450 formulae become invalid, because the cell that they point at has moved but their references remained static. What if you have 50,000 of them? Do you fancy changing every one by hand?

Copying formulae from one cell to other cells as you build a spreadsheet is common, and the absolute referencing is designed to facilitate that. On the other hand, almost no-one wants the referencing to remain static when rows/columns are added/removed.

Here is some extra when the menu option:
Menu/Tools/Options/LIbreOffice calc/General - Expand references... is selected (or not):-

  1. =SUM($A$5:$A$9) is placed in cell A1
  2. (number) 1 is placed in cells A5,A6,A7,A8,A9

  3. A1 = 5

  4. formula: =SUM($A$5:$A$9)

Default (Expand references... is NOT selected):-

Add a row at A5:

  • A1 = 5
  • formula: =SUM($A$6:$A$10)

(or) Add a row at A10:

  • A1 = 5
  • formula: =SUM($A$5:$A$9)

Non-Default (Expand references... IS selected):-

Add a row at A5:

  • A1 = 5
  • formula: =SUM($A$5:$A$10)

(or) Add a row at A10:

  • A1 = 5
  • formula: =SUM($A$5:$A$10)

If this helps then please tick the answer (✔).

edit flag offensive delete link more
0

answered 2015-07-17 00:15:59 +0200

m.a.riosv gravatar image

It works as must be expected.

I think the option:

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

must solve your. It just modify the behaviour.

edit flag offensive delete link more

Comments

Hi m.a.riosv

Always good to come across something new.

That affects rows/columns added next to existing references (If you insert rows or columns in the middle of a reference area, the reference is always expanded). It also does not stop rows being pushed down (if added above) or up (if deleted above).

My answer expanded in the light of this.

Alex Kemp gravatar imageAlex Kemp ( 2015-07-17 00:54:27 +0200 )edit
0

answered 2015-07-17 14:50:24 +0200

lolax gravatar image

How about A1=SUM(INDEX(A1:A500,5):INDEX(A1:A500,500)) ?

edit flag offensive delete link more

Comments

As INDEX doesn't return a reference but a value I cannot understand how the suggested formula should work with the intended result. In fact it should produce an error even in the slightly "internationalized" form (Locales where the comma is used as the decimal delimiter cannot also accept it as a parameter delimiter.):

(A1:) =SUM(INDEX(A1:A500;5):INDEX(A1:A500;500)) .

If working at all it would also adapt in the unwanted way.

@ lolax Do you mind giving additional explanation?

Lupp gravatar imageLupp ( 2015-07-18 12:30:12 +0200 )edit

Hi @Lupp maybe the explanation is in LIbreOffice help: "INDEX returns a sub range, specified by row and column number, or an optional range index. Depending on context, INDEX returns a reference or content."

m.a.riosv gravatar imagem.a.riosv ( 2015-07-18 13:07:52 +0200 )edit

It works for me in LibreOffice Calc or MS Excel.

lolax gravatar imagelolax ( 2015-07-18 14:53:02 +0200 )edit

@m.a.riosv Sorry!

As help texts often are not optimally clear I didn't consult this one. Instead I referred to the mandatory specification ( http://docs.oasis-open.org/office/v1.... ) which doesn't mention the "context sensitivity" causing references to be returned under conditions by INDEX. INDIRECT and OFFSET, however, are specified to return references. A test I also performed must have been spoilt by a typo or whatever.

Lupp gravatar imageLupp ( 2015-07-18 15:08:49 +0200 )edit

@lolax Sorry again. I should have tested more thoroughly. (As I always use OFFSET and/or INDIRECT for similar purposes I was bad in understanding your suggestion.) Sorry again! I can no more edit my wrong comment. (The last few words "...it would also adapt in the unwanted way." should, however, be correct.)

Lupp gravatar imageLupp ( 2015-07-18 15:15:40 +0200 )edit

There is no matter @Lupp, usually I forgot to use it in that way, even more I have read recently that it seems better for performance than e..g OFFSET() because it is not volatile and is recalculated only when precedents changes while OFFSET() is always recalculated.

m.a.riosv gravatar imagem.a.riosv ( 2015-07-18 15:26:21 +0200 )edit

It's okay and thanks for providing those methods.

lolax gravatar imagelolax ( 2015-07-18 15:37:27 +0200 )edit
0

answered 2015-07-18 12:46:11 +0200

Lupp gravatar image

updated 2015-07-18 15:19:47 +0200

We were thoroughly informed of the settings and the working of automatic range adaption on inserting (also deleting) rows (or/and columns).

If the results of such an adaption are not acceptable under the circumstances we have to express our expectations more clearly. This can be done in more than one ways. With regard to the original example some of these are:

(A1):

=SUM(INDIRECT("$A$5"):INDIRECT("$A$500"))
=SUM(INDIRECT("$A$5:A$500"))
=SUM(OFFSET($A$1;5-1;0;500-5+1;1)

The third of these suggestion is by far best adaptable to changing needs. It will, however change automatically into

=SUM(OFFSET($A$2;5-1;0;500-5+1;1))

on inserting a row above the (old) first row. To avoid this, too, you may use:

=SUM(OFFSET(INDIRECT("$A$1");5-1;0;500-5+1;1))

=EDITING=

Please note that parts of my comments on the solution suggested by @lolax were wrong. Sorry1

edit flag offensive delete link more
0

answered 2015-07-18 13:03:42 +0200

pierre-yves samyn gravatar image

updated 2015-07-18 15:49:10 +0200

Hi

Without changing or options or formulas: cut (Ctrl+X) line 1 (or A1), insert line 5, paste line 1 (Ctrl+V)

Regards

edit flag offensive delete link more

Comments

Ctrl-X Ctrl-V doesn't solve this; formulas in unrelated columns are still screwed with, just as if you'd inserted cells. Try it (nine-row simple example: ColumnA = 1 to 9; ColumnC = 1 to 9; ColumnB = =IF(B1=D1,"yes","no") cut+paste columnC causes ColB formulas to change )

Some bonehead must've gone out of their way to write software to recognize what you're pasting is similar to what was cut, and screw with formulas. Maddening garbage. v5.3.3.2

Anyone have a solution?

RogerRamjet gravatar imageRogerRamjet ( 2017-09-06 16:27:56 +0200 )edit

Here's a crap workaround: Cut, Paste, Undo, Paste again

Another is to: Cut, do something else, Paste;
where "do something else" means type some garbage or something into an unrelated cell (evidently you have to make bonehead's software "forget" that you are pasting what was just cut).

RogerRamjet gravatar imageRogerRamjet ( 2017-09-06 16:37:10 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-07-16 23:17:49 +0200

Seen: 2,590 times

Last updated: Jul 18 '15