How do you initialize the values in a circular reference?
 

With complex calculations that require a circular reference, you can have an
error when you open the spreadsheet. It can be fixed by replacing a key value
in the reference to get the calculation going, then put the real calcuation
back in. This is cumbersome--how can you provide a way for the circular
reference calculations to start?

How do you initialize the values in a circular reference?
 

Have you tried going to
Tools -- Options -- [Calculation] tab and ticking the box labeled
"Iterations"? If that box is checked, then circular references are allowed
in the workbook. I don't know why they call it 'Circular Reference" at run
time and "Iterations" in the [Calculation] tab, but they do. Might have been
more 'intuitive' had they labeled the box on the [Calculation] tab something
like "Permit Circular References".

"turbosteve" wrote:

With complex calculations that require a circular reference, you can have an
error when you open the spreadsheet. It can be fixed by replacing a key value
in the reference to get the calculation going, then put the real calcuation
back in. This is cumbersome--how can you provide a way for the circular
reference calculations to start?

How do you initialize the values in a circular reference?
 

In other words, you want to reset the circular reference formula?

What I do is select the cell with the formula. Double click (or function key
F2) then click on the "enter icon" (or hit the Enter key).

--
Biff
Microsoft Excel MVP


"turbosteve" wrote in message
...
With complex calculations that require a circular reference, you can have
an
error when you open the spreadsheet. It can be fixed by replacing a key
value
in the reference to get the calculation going, then put the real
calcuation
back in. This is cumbersome--how can you provide a way for the circular
reference calculations to start?

Steve: Suppose You have your desired initialization values in one column (say A), and You have your cyclic formulae in another column (say B). You can put at the top of your spreadsheet a pair of "reset variables", putting 1 into say C1, and (1-C1) into D1; during the initialization process You will set C1 to 0, then set C1 back to 1. Modify your cyclic formulae to be [(old formula in cell B,n)*C1 + (initialization value in A,n)*D1]. Whenever You want to re-initialize, manually set cell C1 to be 0, then --to compute your cyclic formulae-- reset C1 to be 1.

If You prefer, instead of inputting 0 & 1, You can use text values "yes" & "no", with an appropriate conversion to 0/1 formula, e.g. IF(EXACT(C1,"yes"),1,0).

If needed, You can similarly use this same pair of "reset variables" in other places of the spreadsheet.

Others may know a sweeter way to implement this idea. Hope this helps.

i know that YOU didnt start it to make it a flame war, but check out from your post to mine. 5 post, 5 flaming. this is what im talking about.