Ask Your Question
0

Calc messing up formulas

asked 2015-04-08 07:42:05 +0100

Michael Fontaine gravatar image

updated 2015-04-08 08:57:00 +0100

How do I prevent Calc from changing formulas when I move, insert or delete cells or rows?

Example:

  1. Open/Create a new sheet.
  2. In B2, enter value of 1.
  3. In B3, enter value of 2
  4. In A1, enter formula =B2+B3
  5. Cut B2 and paste in B1.

Suddenly, Calc updates the formula in A1 to =B1+B3!

A similar thing happens if I insert a row: Calc adjusts every since formula that references the old row in order to prevent the calculated value from changing.

Similar to this question, except that deals only with formulas changing when sorting occurs. That is not what I am concerned about.

Here's why this is a big problem: I use Calc to balance various account balances, with accounts running columns and dates of the months running down rows. I initially have transactions I expect to occur, but whenever an unexpected transaction occurs, I need to insert a new row. But unless I go through and verify every individual formula in each balance column, inevitably the formula gets all messed up. Obviously this is a big balance if I rely on the projected future balance to make sure I don't overdraw my account due to the formula being wrong because Calc changed it and I failed to catch it. (Copying and pasting a known good formula is the most reliable, but isn't foolproof because I have different formatting and monthly value resets which prevents me from doing a blanket copy/paste down the whole column.)

Update:

The problem is a bit trickier than simply preventing Calc from changing a formula. This can be accomplished as follows:

First, set up all the formulas the way you want them. For instance, for a running balance column, create the first formula, then copy and paste it down the column as far as needed.

Select all the cells with formulas to fix, and navigate the menus to Edit:Find and Replace.

Search For: \$?([A-Z])\$?([0-9]+)

Replace With: INDIRECT("$1$2")

Other Options:

Check "Current selection only"

Check "Regular Expressions"

Search in: "Formulas"

Now click the "Replace All" button.

But this only protects against the formula from changing if a cell it references moves. If a row is inserted, not changing the formula is incorrect as well. For instance, suppose I have a running balance, where column A contains the balance, and column B contains the transaction amount. Suppose that the formula for B147 is "B146+C147". If I insert a row between row 146 and 147, shifting 147 to 148, then "B146+C147" (an unchanged formula) is wrong because it no longer gives the correct balance in the correct spot. But if the formula isn't protected, Calc changes the formula for B148 (formerly B147) to "B146+C148". THIS IS WRONG TOO! It totally ignores the new row 147: the formula needs to change to "B147+C148", and the new B147 needs to get "B146+C147" which is the original formula for B147. Trying to keep ... (more)

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted
2

answered 2015-04-08 11:57:54 +0100

pierre-yves samyn gravatar image

Hi - You should be able to treat the case of your formula B147 replacing your formula by:

=INDIRECT("B"&ROW()-1)+C147

With this formula the sum is always done by adding the cell above with the right

edit flag offensive delete link more
1

answered 2015-04-08 12:33:29 +0100

karolus gravatar image

This i exactly the expected behavior.

If you move a cell referenced by a Formula the reference in Formula will also move - if you don't want this behavior - don't move ... but copy it and delete afterwards.

edit flag offensive delete link more
1

answered 2015-04-08 10:56:02 +0100

ROSt52 gravatar image

In your example with B2, B3 etc and your example wiht B147, B148 etc, Calc works correct.

If you cut B2 and move it somewhere else Calc corrects the formula to where you move your B2. If you don't want the change in the formula b2->b1, than copy b2 into b1. The formula in A1 remains b1+b2

In the case of a new row inserted in between 146 and 147, Calc again keeps the formula protected because you told Calc that B147 = B146 +C147 and when you insert a row above 147, B147 becomes B148 and C147 becomes C148. However B146 remains unchanged as Calc shifts rows down when you insert a row. Thus the formular changes correctly to be B146+C148 in cell B148

I recommend to have a look at the free guides, which you can download from http://www.libreoffice.org/get-help/d... and study about copying and moving cells and using absolute cell references. Should you still have questions, please feel free to post it.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-04-08 07:42:05 +0100

Seen: 1,471 times

Last updated: Apr 08 '15