Ask Your Question

if function question circular reference [closed]

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

bluefrog gravatar image

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

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 reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-10-11 14:45:58.768799

1 Answer

Sort by » oldest newest most voted

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

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 +0100 )edit

Question Tools

1 follower


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

Seen: 372 times

Last updated: Jul 29 '17