Ask Your Question

if function question circular reference

asked 2017-07-29 22:52:42 +0200

bluefrog gravatar image

updated 2017-07-29 22:53:18 +0200

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.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2017-07-29 23:18:07 +0200

Lupp gravatar image

Circular 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.

edit flag offensive delete link more


thanks, moved function to H1

bluefrog gravatar imagebluefrog ( 2017-07-29 23:44:44 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-07-29 22:52:42 +0200

Seen: 156 times

Last updated: Jul 29 '17