Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenSat, 29 Jul 2017 23:44:44 +0200if function question circular referencehttps://ask.libreoffice.org/en/question/115790/if-function-question-circular-reference/How can I specify the current cell value for a condition that evaluates to false?
For example, function looks as follows
=IF(F1="null",I1, J1 )
The function exists in J1, so if F1 is null, then use the value from I1, otherwise leave J1 as is.
Currently Err:522 appears in J1, since the function refers to itself.Sat, 29 Jul 2017 22:52:42 +0200https://ask.libreoffice.org/en/question/115790/if-function-question-circular-reference/Answer by Lupp for <p>How can I specify the current cell value for a condition that evaluates to false?
For example, function looks as follows</p>
<pre><code>=IF(F1="null",I1, J1 )
</code></pre>
<p>The function exists in J1, so if F1 is null, then use the value from I1, otherwise leave J1 as is.
Currently Err:522 appears in J1, since the function refers to itself.</p>
https://ask.libreoffice.org/en/question/115790/if-function-question-circular-reference/?answer=115793#post-id-115793Circular references are basically against the grain of spreadsheets.
They aren't completely prohibited, however, because they can be used in rare cases for iterative calculations with sufficient reliability. `=IF(ISNUMBER(A1);COS(A1);1)` e.g. can converge to a good approximation of the solution of the equation `x=COS(x)`. Anyway there must be a termination condition. There are two respective parameters that can be set under 'Tools'>'Options'>'LibrOffice Calc'>'Calculate. In the same dialogue you first need to enable circular references.
The example you give does not look like one for fixed-point convergence. Be careful. Many attempts to use conditionally suspended circular references pretend to work as expected under some testing, but fail under real-world conditions.Sat, 29 Jul 2017 23:18:07 +0200https://ask.libreoffice.org/en/question/115790/if-function-question-circular-reference/?answer=115793#post-id-115793Comment by bluefrog for <p>Circular references are basically against the grain of spreadsheets. <br>
They aren't completely prohibited, however, because they can be used in rare cases for iterative calculations with sufficient reliability. <code>=IF(ISNUMBER(A1);COS(A1);1)</code> e.g. can converge to a good approximation of the solution of the equation <code>x=COS(x)</code>. Anyway there must be a termination condition. There are two respective parameters that can be set under 'Tools'>'Options'>'LibrOffice Calc'>'Calculate. In the same dialogue you first need to enable circular references. </p>
<p>The example you give does not look like one for fixed-point convergence. Be careful. Many attempts to use conditionally suspended circular references pretend to work as expected under some testing, but fail under real-world conditions.</p>
https://ask.libreoffice.org/en/question/115790/if-function-question-circular-reference/?comment=115802#post-id-115802thanks, moved function to H1Sat, 29 Jul 2017 23:44:44 +0200https://ask.libreoffice.org/en/question/115790/if-function-question-circular-reference/?comment=115802#post-id-115802