Ask Your Question
0

if function question circular reference

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

1 Answer

Sort by » oldest newest most voted
0

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

Comments

thanks, moved function to H1

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

Question Tools

1 follower

Stats

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

Seen: 213 times

Last updated: Jul 29 '17