Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenWed, 08 Apr 2015 12:33:29 +0200Calc messing up formulashttps://ask.libreoffice.org/en/question/48916/calc-messing-up-formulas/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](http://ask.libreoffice.org/en/question/40686/calc-sort-messing-up-formulas/), 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 all this straight manually is turning out to be a nightmare. How can I make Calc do this automatically for both row insertion and cell move?
Wed, 08 Apr 2015 07:42:05 +0200https://ask.libreoffice.org/en/question/48916/calc-messing-up-formulas/Answer by Michael Fontaine for <div class="snippet"><p>How do I prevent Calc from changing formulas when I move, insert or delete cells or rows?</p>
<p>Example:</p>
<ol>
<li>Open/Create a new sheet.</li>
<li>In B2, enter value of 1.</li>
<li>In B3, enter value of 2</li>
<li>In A1, enter formula =B2+B3</li>
<li>Cut B2 and paste in B1.</li>
</ol>
<p>Suddenly, Calc updates the formula in A1 to =B1+B3!</p>
<p>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.</p>
<p>Similar to <a href="http://ask.libreoffice.org/en/question/40686/calc-sort-messing-up-formulas/">this question</a>, except that deals only with formulas changing when sorting occurs. That is not what I am concerned about.</p>
<p>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.)</p>
<p>Update:</p>
<p>The problem is a bit trickier than simply preventing Calc from changing a formula. This can be accomplished as follows:</p>
<p>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. </p>
<p>Select all the cells with formulas to fix, and navigate the menus to <code>Edit:Find and Replace</code>.</p>
<p>Search For: <code>\$?([A-Z])\$?([0-9]+)</code></p>
<p>Replace With: <code>INDIRECT("$1$2")</code></p>
<p>Other Options:</p>
<p>Check "Current selection only"</p>
<p>Check "Regular Expressions"</p>
<p>Search in: "Formulas"</p>
<p>Now click the "Replace All" button.</p>
<p>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 ...<span class="expander"> <a>(more)</a></span></p></div> https://ask.libreoffice.org/en/question/48916/calc-messing-up-formulas/?answer=48918#post-id-48918First, 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: `\$$1\$$2`
Other Options:
Check "Current selection only"
Check "Regular Expressions"
Search in: "Formulas"
Now click the "Replace All" button.
What this does: all cell references get prefixed with "$" which prevents the reference from being changed. The "\$?" before the column and row sub-regex ensure that if the dollar already exists it won't get duplicated. For instance, "B156" gets changed to "$B$156", but "$B156" also gets changed to the same thing. Wed, 08 Apr 2015 08:25:18 +0200https://ask.libreoffice.org/en/question/48916/calc-messing-up-formulas/?answer=48918#post-id-48918Answer by karolus for <div class="snippet"><p>How do I prevent Calc from changing formulas when I move, insert or delete cells or rows?</p>
<p>Example:</p>
<ol>
<li>Open/Create a new sheet.</li>
<li>In B2, enter value of 1.</li>
<li>In B3, enter value of 2</li>
<li>In A1, enter formula =B2+B3</li>
<li>Cut B2 and paste in B1.</li>
</ol>
<p>Suddenly, Calc updates the formula in A1 to =B1+B3!</p>
<p>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.</p>
<p>Similar to <a href="http://ask.libreoffice.org/en/question/40686/calc-sort-messing-up-formulas/">this question</a>, except that deals only with formulas changing when sorting occurs. That is not what I am concerned about.</p>
<p>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.)</p>
<p>Update:</p>
<p>The problem is a bit trickier than simply preventing Calc from changing a formula. This can be accomplished as follows:</p>
<p>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. </p>
<p>Select all the cells with formulas to fix, and navigate the menus to <code>Edit:Find and Replace</code>.</p>
<p>Search For: <code>\$?([A-Z])\$?([0-9]+)</code></p>
<p>Replace With: <code>INDIRECT("$1$2")</code></p>
<p>Other Options:</p>
<p>Check "Current selection only"</p>
<p>Check "Regular Expressions"</p>
<p>Search in: "Formulas"</p>
<p>Now click the "Replace All" button.</p>
<p>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 ...<span class="expander"> <a>(more)</a></span></p></div> https://ask.libreoffice.org/en/question/48916/calc-messing-up-formulas/?answer=48934#post-id-48934This 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.Wed, 08 Apr 2015 12:33:29 +0200https://ask.libreoffice.org/en/question/48916/calc-messing-up-formulas/?answer=48934#post-id-48934Answer by ROSt52 for <div class="snippet"><p>How do I prevent Calc from changing formulas when I move, insert or delete cells or rows?</p>
<p>Example:</p>
<ol>
<li>Open/Create a new sheet.</li>
<li>In B2, enter value of 1.</li>
<li>In B3, enter value of 2</li>
<li>In A1, enter formula =B2+B3</li>
<li>Cut B2 and paste in B1.</li>
</ol>
<p>Suddenly, Calc updates the formula in A1 to =B1+B3!</p>
<p>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.</p>
<p>Similar to <a href="http://ask.libreoffice.org/en/question/40686/calc-sort-messing-up-formulas/">this question</a>, except that deals only with formulas changing when sorting occurs. That is not what I am concerned about.</p>
<p>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.)</p>
<p>Update:</p>
<p>The problem is a bit trickier than simply preventing Calc from changing a formula. This can be accomplished as follows:</p>
<p>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. </p>
<p>Select all the cells with formulas to fix, and navigate the menus to <code>Edit:Find and Replace</code>.</p>
<p>Search For: <code>\$?([A-Z])\$?([0-9]+)</code></p>
<p>Replace With: <code>INDIRECT("$1$2")</code></p>
<p>Other Options:</p>
<p>Check "Current selection only"</p>
<p>Check "Regular Expressions"</p>
<p>Search in: "Formulas"</p>
<p>Now click the "Replace All" button.</p>
<p>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 ...<span class="expander"> <a>(more)</a></span></p></div> https://ask.libreoffice.org/en/question/48916/calc-messing-up-formulas/?answer=48929#post-id-48929In 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/documentation/ and study about copying and moving cells and using absolute cell references. Should you still have questions, please feel free to post it.
Wed, 08 Apr 2015 10:56:02 +0200https://ask.libreoffice.org/en/question/48916/calc-messing-up-formulas/?answer=48929#post-id-48929Answer by pierre-yves samyn for <div class="snippet"><p>How do I prevent Calc from changing formulas when I move, insert or delete cells or rows?</p>
<p>Example:</p>
<ol>
<li>Open/Create a new sheet.</li>
<li>In B2, enter value of 1.</li>
<li>In B3, enter value of 2</li>
<li>In A1, enter formula =B2+B3</li>
<li>Cut B2 and paste in B1.</li>
</ol>
<p>Suddenly, Calc updates the formula in A1 to =B1+B3!</p>
<p>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.</p>
<p>Similar to <a href="http://ask.libreoffice.org/en/question/40686/calc-sort-messing-up-formulas/">this question</a>, except that deals only with formulas changing when sorting occurs. That is not what I am concerned about.</p>
<p>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.)</p>
<p>Update:</p>
<p>The problem is a bit trickier than simply preventing Calc from changing a formula. This can be accomplished as follows:</p>
<p>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. </p>
<p>Select all the cells with formulas to fix, and navigate the menus to <code>Edit:Find and Replace</code>.</p>
<p>Search For: <code>\$?([A-Z])\$?([0-9]+)</code></p>
<p>Replace With: <code>INDIRECT("$1$2")</code></p>
<p>Other Options:</p>
<p>Check "Current selection only"</p>
<p>Check "Regular Expressions"</p>
<p>Search in: "Formulas"</p>
<p>Now click the "Replace All" button.</p>
<p>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 ...<span class="expander"> <a>(more)</a></span></p></div> https://ask.libreoffice.org/en/question/48916/calc-messing-up-formulas/?answer=48933#post-id-48933Hi - 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 rightWed, 08 Apr 2015 11:57:54 +0200https://ask.libreoffice.org/en/question/48916/calc-messing-up-formulas/?answer=48933#post-id-48933