Recalculating in a circular reference

[John]

I have a circular reference designed to iterate and recalculate when I
change an input cell. When the input cell causes the calculation to result
in a ##NULL value I have to click on one of the cells in the circular
reference, then click on the formula bar and then press enter before it will
recalculate even if the input cell is a safe value. Does anyone know a way
to reset or refresh the cells in the circular reference other than the
onerous one that I have discovered?

Thanks.

 

[driller]

Hi John,

ive been doing such *re-ite-itera-iterarion* past 7 years ago, but i feel a
little bit shaking while having a starting suggestions .....

for me, the input cell is a one precious cell acting as the xenter gateway
of the circular formulation of many cells, sheets, or even with other
worksheets.....at an instant or *open-close* anti-re-calculation process.

for me, the input cell must contain ready made formula!!!
for faster iteration, the input cell must be linked *not only* to one cell...
i make the input cell to function as the *heart of the multi-circular group
of formulation*...
In other words, in a 2D circle- it is located in the vertex and produce rays
of linkage towards the different cells acting as perimeter. For a 2 or 3
group of circles, i place it in the intersecting perimeter.

I often place the input cell in the left uppermost corner <sheetwise> of the
group of circular formulation .

first is I type a starting desirable value in the input cell *not formula*.
then i paste the ready-made formula in the input cell *on the formula bar*
.....
*from left to right then down* ! error results are eliminated one-at-a-time
and towards a smooth logical pattern...

I had no experience working on a circular formulation wherein the perimeter
cells contains *error result* or an IF formula with a "" result.
I found it easier this way in order to be considered as reliable...

regards,
driller

 

[John]

Can anyone else try to answer my question? I know how to set up the circular
reference, but I need to know how to automatically reset the iteration. For
a simple example, say I have a input ratio R and a formula x = SQRT(x) + R.
If you specify R as 10 in cell A1 and in B1 type the formula =SQRT(B1) + A1,
then you have a circular reference. If you allow Excel to iterate the anwer
it solves easily. Thereafter you can change R and it will automatically
recalculate a new answer. Now if you change R to a negative number it gives
a #NUM! error. Then if you change R back to a positive number it will not
recalculate until you click on B1, then click on the formula bar and then
press the Enter key. Because I want to be able to vary the value of R
frequently and I have numerous iterative calculations in my work I am looking
for a better way to handle this. Thanks!

 

[vezerid]

John,

Two things. First, I think the following formula greatly improves your
situation, but I don't know if it will handle all cases without you
needing to repeat the process you described.

=IF(ISERROR(B1)*(A1>0),A1,SQRT(B1)+A1)

Second, you might want to consider the recursive equivalent using one
cell per iteration, which does not need the Iteration flag checked at
Options. You set A1 to contain R, then A2 and down:

=SQRT(A1)+$A$1

From my tests 40 iterations are enough but to make sure you can

retrieve the last result with:

=IF(A40<>A39,"Not resolved",A40)

HTH
Kostis Vezerides